find the third space after an ampersand in cell

**johnmerlino** · 11-09-2010, 01:26 PM

Hey all,

I have this:

Please Login or Register  to view this content.

if no comma before first ampersand, then that means last name is at end of cell, so we take content after third space after the ampersand and place it in front and then place a comma and then take content after ampersand and put directly after comma:

Please Login or Register  to view this content.

And then in next column, we do above but instead this time we take content before ampersand and stick it after comma:

Please Login or Register  to view this content.

I'm stuck trying to figure out how to get content after third space after ampersand:

Please Login or Register  to view this content.

Thanks for response.

**MarvinP** · 11-09-2010, 01:40 PM

Hi John,

If KNUD J & MARIA L HOSTRUP is in A1 then

Please Login or Register  to view this content.

returns location of third space.

hth.

**johnmerlino** · 11-09-2010, 10:32 PM

Yeah but that's not third space after an ampersand. That's third space in cell. How do I find third space after ampersand so I can extract the last name?

**johnmerlino** · 11-09-2010, 10:42 PM

While this formula pulls the last occurrence:

Please Login or Register  to view this content.

I am trying to pull everything after three spaces after ampersand. because the last name might be multiple words.

**squiggler47** · 11-09-2010, 11:01 PM

Do these work for you?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**squiggler47** · 11-09-2010, 11:22 PM

Taking an assumption of 2 cases from your question :-

KNUD J & MARIA L HOSTRUP
HOSTRUP, KNUD J & MARIA L

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Will return
HOSTRUP, KNUD J
HOSTRUP, MARIA L

Given Either Case!

**johnmerlino** · 11-09-2010, 11:37 PM

Thanks for response.

It almost works.

In cases, where there's no ampersand and comma, I would like it just to return length of cell:
Sarah Ann Little Junior
would just return
Sarah Ann Little Junior
since there's no ampersand and comma.

Your solution turns it into this:
Junior,Sarah Ann Little

Thanks for response.

**johnmerlino** · 11-09-2010, 11:48 PM

I'm thinking of sticking a condition like this:
=IF(AND(A20<>",",A20<>"&"),TRUE,FALSE)

before your recommended condition

Problem is the formula I have above doesn't work as expected. I'm trying to say if the cell does not contain a comma and ampersand then just return length of cell, otherwise run your condition.

**squiggler47** · 11-09-2010, 11:52 PM

your conditions are looking for the cell to contain just "&" or "," :-

=IF(FIND(",",B4&",")=FIND("&",B4&"&"),TRUE,FALSE)

This avoids a not found error,and can only be true if the string contains neither a , or a &

combining these :-

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**johnmerlino** · 11-09-2010, 11:54 PM

This might work:
=IF(AND(ISERROR(FIND(",",A20)),ISERROR(FIND("&",A20))),TRUE,FALSE)

**squiggler47** · 11-10-2010, 12:15 AM

As an alternative a 3 way IF :-

=CHOOSE(SIGN((FIND(",",B2&",")-FIND("&",B2&"&")))+2,1,2,3)

giving :-

Please Login or Register  to view this content.

And

Please Login or Register  to view this content.

**johnmerlino** · 11-10-2010, 01:09 AM

Thanks for the replies.

Your excel formulas are too complicated for me to understand. You mention that this:

Please Login or Register  to view this content.

will return this:
HOSTRUP, MARIA L

I'm looking to do:
=IF(ISERR(FIND("&",A1,1)),"",IF(AND(ISERROR(FIND(",",A1)),IF(ISNUMBER(FIND("&",A1)))),RUN YOUR CODE,LEFT(A1,FIND("&",A1,1)-1)))

So basically if it doesn't find an ampersand, return blank cell. If it doesn't find comma but finds ampersand, run your code. Otherwise if there's an ampersand and a comma, then run: LEFT(A1,FIND("&",A1,1)-1)))

I just can't get your code integrating into the above formula, because it's too complicated for me to understand where it begins and ends in regards to how I intend it to work above.

Thanks for response.

**squiggler47** · 11-10-2010, 01:18 AM

The problem is you keep asking parts of the overall problem, then adding more problems to it!

The easiest way is to provide a list of all possible cases you have, rather than try to solve each one individually and combine the results, you are quickly going to come up to the nesting limit!

Otherwise you are just going to keep posting more situations!

**johnmerlino** · 11-10-2010, 01:25 AM

Once I fill in "Run your code" with the formula that returns like in your example "HOSTRUP,MARIA L"
then that would be it.

This is the only situations that could be:
=IF(ISERR(FIND("&",A1,1)),"",IF(AND(ISERROR(FIND(",",A1)),IF(ISNUMBER(FIND("&",A1)))),RUN YOUR CODE,LEFT(A1,FIND("&",A1,1)-1)))

If no ampersand, return empty cell, as shown above, if no comma but ampersand exists, then run your code, as shown above, otherwise that must mean that must mean that there is no ampersand but there is a comma so we just return LEFT(A1,FIND("&",A1,1)-1).
And that would be it.

In other words, all i really need is a formula to grab, for example, "HOSTRUP,MARIA L" right in the spot where i say "run your code".

**johnmerlino** · 11-10-2010, 09:56 AM

Trying to extract the portion that I need:

Please Login or Register  to view this content.

This tells me "The Formula You Typed Contains Errors" but it doesn't highlight anything in particular.

**johnmerlino** · 11-10-2010, 10:02 AM

=IF(ISERR(FIND("&",A1,1)),"",IF(AND(ISERROR(FIND(",",A1)),IF(ISNUMBER(FIND("&",A1)))),ERROR OCCURS HERE,LEFT(A1,FIND("&",A1,1)-1)))

Where I say error occurs here, this is what's there:

LEFT(B3,FIND(",",B3&",")-1)&","&RIGHT(B3,LEN(B3)-FIND("&",B3&"&")),RIGHT(B3,LEN(B3)-SEARCH("@",SUBSTITUTE(B3," ","@",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))&", "&SUBSTITUTE(RIGHT(B3,LEN(B3)-FIND("&",B3&"&")-1),RIGHT(B3,LEN(B3)-SEARCH("@",SUBSTITUTE(B3," ","@",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))),""))

which turns this:
KNUD J & MARIA L HOSTRUP

into this:
HOSTRUP,MARIA L

All it's supposed to do is if no ampersand, return empty cell, if no comma but ampersand exists, then the code which is containing errors right now and excel isn't telling me what problem is, and if there is no ampersand but there is a comma so we just return LEFT(A1,FIND("&",A1,1)-1).

**squiggler47** · 11-10-2010, 10:07 AM

here is the file I did the working in, highlighted in yellow at the right are the final formulas, and to the left highlighted in yellow are the parial formulas used to construct the one formula!