formula to find the position of the "previous" instance of a character within a search string

[ellison]

Hi, we are trying to clean some data up & extract text which ends: 883;" from search strings...

EG we would like to extract 2N5883 out of cell B2 = ;XYZ12;2N5883;B73304001;B73304;;;;;;;;

The plan is to use the MID function with a helper column:

a) SEARCH("883;",B2)+3 to find the position of the character in the 883;
in the above instance, it would return "14" as that ";" is the 14th character

b) MID function:

text will be the search string = B2
start_num will be: the position of the ";" which is to the "to the left" the 14th character (*********no idea how to do this*******)
num_chars will be: 14 minus whatever number is returned by the start_num

c) end result to hopefully be:

MID(B2,14,14-7)
=2N5883

So if somebody could help us to find the "position of the character to the left of its next occurrence" that would be really appreicated!

Best
Neil
PS need to find a formula as opposed to macro please

 

[Fluff]

Howabout
=TRIM(RIGHT(SUBSTITUTE(LEFT(B2,SEARCH("883;",B2)+2),";",REPT(" ",200)),200))

 

[ellison]

wow, that is BRILLIANT!!!! CASE CLOSED!!!!!

joking aside, many thanks indeed.

warm regards

Neil

 

[Fluff]

Glad to help & thanks for the feedback