Find 2 different characters in text string

**shanevo6** · 06-10-2011, 10:50 AM

I have some data that I need to extract numbers from, I'm nearly there but don't know how to use the find function for more than one character that may be in the string, it will be either a "S" or "/". I can get it to find one of them but can't work out how to look for the possibility of either in the string?

The attached sheet will help explain?

Rgds
Shane

**NBVC** · 06-10-2011, 10:58 AM

Use formula in B2:

=LEFT(A2,(FIND("/",SUBSTITUTE(A2,"S","/")))-1)

and in D2:

=RIGHT(A2,LEN(A2)-SEARCH("/",SUBSTITUTE(A2,"S","/")))

**shanevo6** · 06-10-2011, 04:45 PM

Many thanks, is it possible to connect the formulas in cell B2 &C2 instead of having 2 seperate formulas?

**NBVC** · 06-10-2011, 09:44 PM

Yes, try:

=IF(LEFT(A2,(FIND("/",SUBSTITUTE(A2,"S","/")))-1)>300,RIGHT(LEFT(A2,(FIND("/",SUBSTITUTE(A2,"S","/")))-1),3))

**shanevo6** · 06-12-2011, 08:14 AM

Many thanks NVBC, I know I've marked as SOLVED but is it possible to limit the characters in D2 to 3 as I've just realised that some text has a letter at the end eg 8502/204W, I want to lose the 'W'.

Thanks once again
Shane

**NBVC** · 06-30-2011, 04:54 PM

I just noticed that added request... usually it is best to post a new post rather than add to your last post, since most of don't look back unless an apparent new request has been made....

Anyway, if you still need it.. If only a W can happen.. try:

=SUBSTITUTE(RIGHT(A2,LEN(A2)-SEARCH("/",SUBSTITUTE(A2,"S","/"))),"W","")

If it is always 3 digits, then

=LEFT(RIGHT(A2,LEN(A2)-SEARCH("/",SUBSTITUTE(A2,"S","/"))),3)