Hey all,
I have this:
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:KNUD J & MARIA L HOSTRUP
And then in next column, we do above but instead this time we take content before ampersand and stick it after comma:HOSTRUP,MARIA L
I'm stuck trying to figure out how to get content after third space after ampersand:HOSTRUP,KNUD J
Thanks for response.=IF(ISERROR(FIND(",",LEFT(A36,FIND("&",A36)-1),1)),CONCATENATE ,FALSE)
Hi John,
If KNUD J & MARIA L HOSTRUP is in A1 then
returns location of third space.=FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)
hth.
One test is worth a thousand opinions.
Click the * below to say thanks.
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?
While this formula pulls the last occurrence:
I am trying to pull everything after three spaces after ampersand. because the last name might be multiple words.=RIGHT(A36,LEN(A36)-SEARCH("@",SUBSTITUTE(A36," ","@",LEN(A36)-LEN(SUBSTITUTE(A36," ","")))))
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," ",""))))),""))
Regards
Darren
Update 12-Nov-2010 Still job hunting!
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!
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))Will return=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," ",""))))),""))
HOSTRUP, KNUD J
HOSTRUP, MARIA L
Given Either Case!
Regards
Darren
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!
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.
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.
Last edited by johnmerlino; 11-09-2010 at 10: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 :-
=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," ",""))))),"")))
Regards
Darren
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!
This might work:
=IF(AND(ISERROR(FIND(",",A20)),ISERROR(FIND("&",A20))),TRUE,FALSE)
As an alternative a 3 way IF :-
=CHOOSE(SIGN((FIND(",",B2&",")-FIND("&",B2&"&")))+2,1,2,3)
giving :-
And=CHOOSE(SIGN((FIND(",",B2&",")-FIND("&",B2&"&")))+2,LEFT(B2,FIND(",",B2&",")-1)&", "&TRIM(SUBSTITUTE(SUBSTITUTE(B2,LEFT(B2,FIND(",",B2&",")),""),RIGHT(B2,LEN(B2)-FIND("&",B2&"&")+1),"")),B2,RIGHT(B2,LEN(B2)-SEARCH("@",SUBSTITUTE(B2," ","@",LEN(B2)-LEN(SUBSTITUTE(B2," ","")))))&", "&LEFT(B2,FIND("&",B2&"&")-1))
CHOOSE(SIGN((FIND(",",B2&",")-FIND("&",B2&"&")))+2,LEFT(B2,FIND(",",B2&",")-1)&","&RIGHT(B2,LEN(B2)-FIND("&",B2&"&")),"",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," ",""))))),""))
Regards
Darren
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!
Thanks for the replies.
Your excel formulas are too complicated for me to understand. You mention that this:
will return this:=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," ",""))))),""))
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.
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!
Regards
Darren
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!
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".
Last edited by johnmerlino; 11-10-2010 at 12:28 AM.
Trying to extract the portion that I need:
This tells me "The Formula You Typed Contains Errors" but it doesn't highlight anything in particular.=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," ",""))))),""))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks