find the third space after an ampersand in cell

Hi John,

If KNUD J & MARIA L HOSTRUP is in A1 then

=FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)

returns location of third space.

hth.

Do these work for you?

=IF(FIND(",",B2&",")<FIND("&",B2&"&"),TRUE,RIGHT(B2,LEN(B2)-SEARCH("@",SUBSTITUTE(B2," ","@",LEN(B2)-LEN(SUBSTITUTE(B2," ","")))))&", "&LEFT(B2,FIND("&",B2&"&")-1))

=IF(FIND(",",B2&",")<FIND("&",B2&"&"),TRUE,RIGHT(B2,LEN(B2)-SEARCH("@",SUBSTITUTE(B2," ","@",LEN(B2)-LEN(SUBSTITUTE(B2," ","")))))&", "&SUBSTITUTE(RIGHT(B2,LEN(B2)-FIND("&",B2&"&")-1),RIGHT(B2,LEN(B2)-SEARCH("@",SUBSTITUTE(B2," ","@",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))),""))

Taking an assumption of 2 cases from your question :-


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

=IF(FIND(",",B3&",")<FIND("&",B3&"&"),LEFT(B3,FIND(",",B3&",")-1)&", "&TRIM(SUBSTITUTE(SUBSTITUTE(B3,LEFT(B3,FIND(",",B3&",")),""),RIGHT(B3,LEN(B3)-FIND("&",B3&"&")+1),"")),RIGHT(B3,LEN(B3)-SEARCH("@",SUBSTITUTE(B3," ","@",LEN(B3)-LEN(SUBSTITUTE(B3," ","")))))&", "&LEFT(B3,FIND("&",B3&"&")-1))

=IF(FIND(",",B3&",")<FIND("&",B3&"&"),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," ",""))))),""))

Will return
HOSTRUP, KNUD J
HOSTRUP, MARIA L

Given Either Case!

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.

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 :-

=IF(FIND(",",B4&",")=FIND("&",B4&"&"),B4,IF(FIND(",",B4&",")<FIND("&",B4&"&"),LEFT(B4,FIND(",",B4&",")-1)&", "&TRIM(SUBSTITUTE(SUBSTITUTE(B4,LEFT(B4,FIND(",",B4&",")),""),RIGHT(B4,LEN(B4)-FIND("&",B4&"&")+1),"")),RIGHT(B4,LEN(B4)-SEARCH("@",SUBSTITUTE(B4," ","@",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))&", "&LEFT(B4,FIND("&",B4&"&")-1)))

=IF(FIND(",",B4&",")=FIND("&",B4&"&"),"",IF(FIND(",",B4&",")<FIND("&",B4&"&"),LEFT(B4,FIND(",",B4&",")-1)&","&RIGHT(B4,LEN(B4)-FIND("&",B4&"&")),RIGHT(B4,LEN(B4)-SEARCH("@",SUBSTITUTE(B4," ","@",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))&", "&SUBSTITUTE(RIGHT(B4,LEN(B4)-FIND("&",B4&"&")-1),RIGHT(B4,LEN(B4)-SEARCH("@",SUBSTITUTE(B4," ","@",LEN(B4)-LEN(SUBSTITUTE(B4," ",""))))),"")))

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!

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".

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!