Search/extract text

Hello..

I'm trying to figure out a better way to extract specific text from a text string. Right now I have 2 steps. I would like to have only 1 step..

For example:

A1 C:\Elandata\DataSet\121911B\QC1

B1 =MID(A1,21,7) => this gives me 121911B

Now I want to search the 121911B to see if the letter is A, B, C or D. I'm using:

=IF(COUNT(SEARCH("A",B1)),"A",IF(COUNT(SEARCH("B",B1)),"B",IF(COUNT(SEARCH("C",B1)),"C",IF(COUNT(SEARCH("D",B1)),"D",""))))

This will give a result of just A B C D. In this case it will give me a "B"

Now the 121911B will change depending on the date and cycle. ie. could be 011311A or 011712D.. etc.

Is there a way to do this in 1 step?

Originally Posted by Special-K

Assuming there are always four seperators of "\" in the string and the character you are looking for is just before the fourth "\" try this

=MID(A1,SEARCH(CHAR(127),SUBSTITUTE(A1,"\",CHAR(127),4))-1,1)

That worked great.. Thanks you!!!