Finding multiple values in text string

[mrzoogle]

Hi All,

I would like to get your assist on how to get multiple value from a text string.

For example, my text string would look something like this:

1. A - B - Placeholder - C - Placeholder
2. A - D - Placeholder - B - Placeholder
3. A - D - Placeholder - Placeholder - B - Placeholder

A, B, C, D are the keywords I need to extract from these string in a format like this

1. A - B - C
2. A - D - B
3. A - D - B

These A, B, C & D are in random position within text which I am finding it difficult to work it out.

Currently I am using this formula for first row =(MID(C2,FIND("A",C2),LEN("A")))&"-"&(MID(C2,FIND("B",C2),LEN("B")))&"-"&(MID(C2,FIND("C",C2),LEN("C")))

But I am not sure how to automate this formula to automatically find these value in the string.

I have also attached the example file for your reference.

Thanks for your time and hope to hear on this soon.

Kind Regards,

Zoogle

 

[Sajan]

Hello,
Not sure if the placeholder is the actual string, or whether that can be various strings. If it is a constant string, try:
=SUBSTITUTE(C2," - Placeholder","")

Cheers,
Sajan.

 

[mrzoogle]

Morning Sajan,

Thanks for taking your time in looking into my issue.

Yes the Placeholder can be various strings, apologies for not being very clear...

Is there any way around this?

Kind Regards,

Zoogle.

 

[kchiba]

Can you provide an actual example of what the data looks like and do you have a list of the keywords that you are looking to extract?

 

[mrzoogle]

Thanks, below is the data:

1. IB - History - John - IN - Nov - 18-02
2. History - IB - IN - Paul - Dec - 01
3. IB - IN - Math - Jan - 14 - 20
4. PR - ENG - 30 - 01 - STU
5. IB - IN - Jan - Susan - 01 - 30
6. IB - IN - Math - 30 - 01

List of keywords need extracted in below format:

IB, IN, Nov, Dec, Jan, PR, ENG, STU

1. IB - IN - Nov
2. IB - IN - Dec
3. IB - IN - Jan
4. PR - ENG - STU
5. IB - IN - Jan
6. IB - IN

Thanks again for looking into my issue.

Kind Regards,

Zoogle.

 

[Xiq]

Hi mrzoogle,

If you have a fixed (small) amount of keywords, then you can try this formula:

=SUBSTITUTE(TRIM(IF(ISERR(FIND("IB",$A1)),"","IB ")&IF(ISERR(FIND("IN",$A1)),"","IN ")&IF(ISERR(FIND("Nov",$A1)),"","Nov ")&IF(ISERR(FIND("Dec",$A1)),"","Dec ")&IF(ISERR(FIND("Jan",$A1)),"","Jan ")&IF(ISERR(FIND("PR",$A1)),"","PR ")&IF(ISERR(FIND("ENG",$A1)),"","ENG ")&IF(ISERR(FIND("STU",$A1)),"","STU "))," "," - ")

Here is the same formula, but now for easy reading:

=SUBSTITUTE(TRIM(
IF(ISERR(FIND("IB",$A1)),"","IB ")&
IF(ISERR(FIND("IN",$A1)),"","IN ")&
IF(ISERR(FIND("Nov",$A1)),"","Nov ")&
IF(ISERR(FIND("Dec",$A1)),"","Dec ")&
IF(ISERR(FIND("Jan",$A1)),"","Jan ")&
IF(ISERR(FIND("PR",$A1)),"","PR ")&
IF(ISERR(FIND("ENG",$A1)),"","ENG ")&
IF(ISERR(FIND("STU",$A1)),"","STU ")
)," "," - ")

 

[Xiq]

Here is another one:

=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(INT(NPV(-0.9,,ISNUMBER(SEARCH({"IB","IN","Nov","Dec","Jan","PR","ENG","STU"},$A1))*{1,2,3,4,5,6,7,8})),0,""),1,"IB "),2,"IN "),3,"Nov "),4,"Dec "),5,"Jan "),6,"PR "),7,"Eng "),8,"STU "))," "," - ")

 

[mrzoogle]

Morning Xiq,

First of all, thank you very much for looking into my issue.

Your formula works perfect!! would have never thought we could use substitute to solve this.

Though I am not sure I understand your second formulae structurally. Would you mind explaining to me please, if you have some time.

Thanks again for your time and help.

Kind Regards,

Zoogle.

 

[Xiq]

Hi mrzoogle,

Some say there is magic involved (looking at NARAYANK991). I think the are correct!

Check out this post:
http://chandoo.org/forum/threads/removing-all-charcters-from-cell-but-numbers.13352/#post-78863