Excel formulae to extract text string from a cell

[simongilder]

Hi,

I am having problems with an Excel formula, I was wondering if anyone can help?
In column A, I have cells with a mix of numbers and text. The string is built according to:

Company Name + B/S+ Number + Customer Name + Reference

Rus-Hydro B 120 Smith 123456789


All elements can vary in length. I want to extract the Customer Name(eg Smith). I considered the Find and Mid function combination, but because everything varies, I can't get it to work.

Can anybody help?

Many thanks

Simon

 

[Rick Rothstein]

See if this formula works for you...

=MID(LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1),FIND(" ",A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))+1,99)

 

[simongilder]

Hi,

Unfortunately, no. The first part of the string would be OK though.

So, either Sampo, or Sampo Bank would work fine. But I couldn't work with "Bank" later on. ((

Many thanks for your help

Simon

 

[jackhandey]

Alright, does this one work? It's not quite so much nesting...just long and ugly

=IF(ISNUMBER(VALUE(LEFT(MID(B3,FIND("$",SUBSTITUTE(B3," ","$",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))-2))+1,FIND("$",SUBSTITUTE(B3," ","$",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))-FIND("$",SUBSTITUTE(B3," ","$",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))-2))-1),1))),RIGHT(MID(B3,FIND("$",SUBSTITUTE(B3," ","$",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))-2))+1,FIND("$",SUBSTITUTE(B3," ","$",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))-FIND("$",SUBSTITUTE(B3," ","$",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))-2))-1),LEN(MID(B3,FIND("$",SUBSTITUTE(B3," ","$",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))-2))+1,FIND("$",SUBSTITUTE(B3," ","$",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))-FIND("$",SUBSTITUTE(B3," ","$",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))-2))-1))-FIND(" ",MID(B3,FIND("$",SUBSTITUTE(B3," ","$",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))-2))+1,FIND("$",SUBSTITUTE(B3," ","$",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))-FIND("$",SUBSTITUTE(B3," ","$",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))-2))-1))),MID(B3,FIND("$",SUBSTITUTE(B3," ","$",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))-2))+1,FIND("$",SUBSTITUTE(B3," ","$",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))-FIND("$",SUBSTITUTE(B3," ","$",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))-2))-1))

This could be simplified greatly if you're willing to have a helper column that could hold an intermediate value, by the way.

 

[simongilder]

Many thanks Jackhandey. It's as near as perfect, dammit! I will try to disect it when I get a chance to understand how it is working.

All the best

Simon

 

[Rick Rothstein]

Many thanks Jackhandey. It's as near as perfect, dammit! I will try to disect it when I get a chance to understand how it is working.

Just wondering if you tried the formula I posted in Message #11 yet as I am pretty sure it will do exactly what you asked for? If I am right (that it does what you want), then, with 77 less function calls, it should be much more efficient than the one Jack posted.

 

[simongilder]

Hi Rick

Thanks for responding. It did work - again, not 100% perfect, but it certainly produced that were more than good enough for me to work with. I guess this is just an exceptionally tricky task, given so many variables. I do appreciate everyone's help.


All the best

Simon

 

[NiceCyte]

Is it safe to assume that the name itself won't have spaces? If not, try this:

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

This seems to only work with 1 name, like Smith. If it was Robert Smith, then... well... not.

 

[Rick Rothstein]

Hi Rick

Thanks for responding. It did work - again, not 100% perfect, but it certainly produced that were more than good enough for me to work with. I guess this is just an exceptionally tricky task, given so many variables. I do appreciate everyone's help.

Can you show me some of the text strings it did not work for and, specifically, show me what you wanted from the formula for each of them. Seeing real examples that did not work plus seeing what should have been the result, will allow me to possibly modify the formula to account for them.

 

[simongilder]

Thanks NiceCyte

Simon

 

[jackhandey]

Rick's solution is light years more elegant than mine... but I'm guessing the issues come up if there is a number contained within the company name? If that's the case, try this:

=MID(LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1),FIND(" ",A1,MIN(IF(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")>IFERROR(FIND(" B ",A1),FIND(" S ",A1)),FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"),99)))+1,99)

It's the same as Rick's, I just made it ignore numbers that occur before the " B " or " S ".