Excel formulae to extract text string from a cell

if there are no spaces in your company names then this could work

=TRIM(MID(A1;FIND(CHAR(7);SUBSTITUTE(A1;" ";CHAR(7);3));FIND(CHAR(7);SUBSTITUTE(A1;" ";CHAR(7);4))-FIND(CHAR(7);SUBSTITUTE(A1;" ";CHAR(7);3))))

As the uncertainty goes up, so does the ugliness... but here's my attempt. Obviously, replace B3 with the relevant cell for you:

=MID(B3,FIND(" ",B3,IF(ISNUMBER(FIND(" S ",B3)),FIND(" S ",B3),FIND(" B ",B3))+3)+1,(FIND(" ",B3,FIND(" ",B3,IF(ISNUMBER(FIND(" S ",B3)),FIND(" S ",B3),FIND(" B ",B3))+3)+1)-(FIND(" ",B3,IF(ISNUMBER(FIND(" S ",B3)),FIND(" S ",B3),FIND(" B ",B3))+3)))-1)

The idea here is to set an anchor point based on " S " or " B ", which is always followed by a number (with no spaces), which is always followed by a space, then the name (with no spaces), then another space. If those assumptions hold true, this should work. It avoids the problem of a company name having spaces, but does not account for spaces that may appear in the number field or the name.

I guess a more sophisticated approach would be to first figure out how many spaces there are and use the space after the number and the last space as the binding criteria - that would account for spaces in the name...

Thanks hippiehacker.

Hi Jackhandey

That's just the ticket. Only, I have discovered another slight complication. Some of the values in the Company name can end with an "S". In this case, it retrns the Number. In other cases, it works fine.

Here is an example of the offending combination:

Ooooh, that's nasty lol. I'll think some more on it, but I imagine one of the elite users will have a much more elegant solution.

In that last example, would you want it to return "Sampo Bank" or just Sampo?

Hi Jackhandey

Ideally, "Sampo Bank", but I'd be happy with "Sampo".

))

Many thanks!

Simon

Alright, this has to be one of the ugliest things I've ever written, but I think it works... it does for the two example above, anyway, and it returns "Sampo Bank" instead of just "Sampo"...

=MID(B3,FIND("$ ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,0,"$"),1,"$"),2,"$"),3,"$"),4,"$"),5,"$"),6,"$"),7,"$"),8,"$"),9,"$"))+2,(FIND(" $",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,0,"$"),1,"$"),2,"$"),3,"$"),4,"$"),5,"$"),6,"$"),7,"$"),8,"$"),9,"$"),FIND("$ ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,0,"$"),1,"$"),2,"$"),3,"$"),4,"$"),5,"$"),6,"$"),7,"$"),8,"$"),9,"$"))))-FIND("$ ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,0,"$"),1,"$"),2,"$"),3,"$"),4,"$"),5,"$"),6,"$"),7,"$"),8,"$"),9,"$"))-2)

It replaces all numbers with a dollar sign and then uses the "$ " and " $" strings as the boundaries for extraction. However, it just occurred to me that if a company name ends in a number, this will blow up... grrr.

Edit - the potential fix for that would be to search for "$$$ " and " $$$" instead... what's the minimum number of numbers that can appear for each of the sets of numbers?

Hi Jackhandey

Thanks so much for that. But unfortunately I get an error - there are too many nesting levels for my version of Excel. Strange, as I saved it as an Excel 2013 file.

Perhaps it would be easier just to go for the "Sampo" result. That would be fine for me.

Thanks again

Simon

Would the result of just "Bank" be acceptable?