Extracting 2nd word from cell when it's also the last?

[BiggusDoggus]

Hi

I have had a good read of previous threads and can't find what I'm after...

I have a column of company names - column M.

Column X, I have =LEFT(M2,FIND(" ",M2)-1), which is fine, brings back first word in the company name.

Column Y, I need to extract the second word in the name - I'm using =MID($M2,FIND(" ",$M2,1)+1,FIND(" ",$M2,FIND(" ",$M2,1)+1)-(FIND(" ",$M2,FIND(" ",$M2,1))), which is also fine....so long as the company name is more than 2 words long!

For example, ABC Ltd isn't bringing back Ltd, it's #VALUE!. I'm assuming it's because there isn't actually a "mid"?

What I need is a formula that brings back the second word regardless of how many words in the name. Or, in case of error, bring back the last word, otherwise the second word. Either would be fine.

I've tried =IFERROR((TRIM(RIGHT(SUBSTITUTE($M2," ",REPT(" ",255)),255))),MID($M2,FIND(" ",$M2,1)+1,FIND(" ",$M2,FIND(" ",$M2,1)+1)-(FIND(" ",$M2,FIND(" ",$M2,1)))) - however, it appears #VALUE! isn't in fact an error, so it only ever brings back the final word.


Can anybody assist please...many thanks!

 

[hyperchondriac]

Is this a one-off job on a static list, or are there lots of lists/does the list change or update?

If the former you could use Excel's text to columns feature using a space as the delimiter. Otherwise, I have some ideas how you could do it with a formula but need to check them out when I am back at a computer with Excel.

HC

 

[Ingolf]

Try this:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),2*99-98,99))

If you need third word change that red 2 in 3, for fourth change with 4 and so on.

 

[BiggusDoggus]

Try this:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),2*99-98,99))

If you need third word change that red 2 in 3, for fourth change with 4 and so on.

That is awesome - works perfectly. And so much more concise than anything else I had!

Many thanks.

 

[BiggusDoggus]

Sorry, I have one more question relating to the above please.

The full context of my situation is this.

I have 2 sets of data, involving company names. One is in column O, the other in column M. I need to check if a company that is in column M is also in column O or not.

Most of the time, the names match - so ABC Moving Places Ltd in one is ABC Moving Places Ltd is in the other.

However, because the data has been entered into the two sets of data at different times, by different people, there are some variations.

For example, MMM Enterprises Ltd could be in one, but has been entered as MMM Enterprises (2015) Ltd in the other - they are, in fact, the same company.

So, using the formula provided by Ingolf I have 2 columns, A & B, which each have the 1st & 2nd words of the company name in column M, respectively.

What I need is a formula that checks column O to confirm if the combination of both values in A & B exist in a single cell.

It would most likely be a variation on =COUNTIF($O:$O,"*"&A1&"*")>0, which tells me if (using the example above) "MMM" (A1) exists in column O - I just need to expand it to see if both "MMM" and "Enterprises" (A2) exist in the same cell anywhere in column O, but am not sure how to.

Hope this makes sense, and again thanks for your help!

 

[BiggusDoggus]

Or maybe something like =SUM(COUNTIFS(O:O, {"*"&A2&"*","*"&B2&"*"}))) - except Excel doesn't like this for some reason.

 

[BiggusDoggus]

Never mind - worked it out: =SUM(COUNTIF(O:O,"*"&A1&"*"&"*"&B1&"*"))>0

Of course, if anybody has a more refined version of the above I will happily accept it!

 

[BiggusDoggus]

Ok, another problem sorry.

So I need to bring back a client number based on multiple criteria, including the wild card formula for the client name as above, and the address.

To demonstrate:

Sheet1
Column A has the first word in the client's name
Column B has the second word in the client's name
Column C has the client's address
Column D is where I have the formula to bring back the client number

Sheet2
Column A has the client names
Column B has their addresses
Column C has their client numbers

The formula I currently have in Sheet1 is: =SUMPRODUCT(('Sheet2'!A:A="*"&A1&"*"&"*"&B1&"*")*('Sheet2'!B:B=C1)*('Sheet2'!C:C))

I only get 0 coming back though. The issue is the wildcards for the client name - evaluating the formula shows that's where it's returning "False" when I know for a fact it should be True.
According to the evaluation, it says SUMPRODUCT(("ABC Surveying (2015) Ltd"="*ABC**Surveying*"), which then returns FALSE.
Which is perplexing, because using those same wildcards in the COUNTIF from earlier works perfectly in identifying that the client exists!

Example data:
Sheet1
A1=ABC
B1=Surveying
C1 = 23 Racecourse Rd

Sheet2
A1 = ABC Surveying (2015) LTD
B1 = 23 Racecourse Rd
C1 = 855456

Just a reminder – the reason I need to use wildcards is the original data in Sheet1 has variations for the name of the same clients. Using the above example, in Sheet1, the client name is ABC Surveying Specialists (2012) Ltd. So I can’t do a direct match search.

Once again – any assistance greatly appreciated!

 

[BiggusDoggus]

Sorry, it returns #VALUE, not 0.

 

[BiggusDoggus]

Man, I should just learn to persevere and eventually I'll get there myself!

=SUMIFS('Sheet2'!C:C,'Sheet2'!A:A,("*"&A1&"*"&"*"&B1&"*"),'Sheet2'!B:B,C1)

My apologies again - as you were.