-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Find Function Question
- Thread starter Mr Walker
- Start date
Yogi Anand
MrExcel MVP
- Joined
- Mar 12, 2002
- Messages
- 11,454
Hello Mr Walker:
Look at the following simulation for the situation where there are two space characters in the name.
If you need a solution for a different condition, post back with more details and let us take it from there.
Look at the following simulation for the situation where there are two space characters in the name.
| Book1 | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | JamesFrancisGray | Gray,JamesFrancis | ||||
Sheet3 | ||||||
If you need a solution for a different condition, post back with more details and let us take it from there.
Upvote
0
NateO
Legend
- Joined
- Feb 17, 2002
- Messages
- 9,700
Hello, I know this is resolved, but I'd still like to throw up the following:
Edit: The function didn't come out so well above, it looks like:
For A1 (single line obviously).
| Parse Names.xls | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | JamesFrancisGray | Gray,JamesFrancis | ||||
| 2 | RoseSarah-JaneOlson | Olson,RoseSarah-Jane | ||||
| 3 | MikeB.ClarkGriswald | Griswald,MikeB.Clark | ||||
| 4 | JoeClark | Clark,Joe | ||||
| 5 | TestThisPleaseThanks | Thanks,TestThisPlease | ||||
| 6 | RobertWDowneyJr. | DowneyJr.,RobertW | ||||
| 7 | JoePaulDirtIV | DirtIV,JoePaul | ||||
| 8 | AlanMichaelStephanJordanIII | JordanIII,AlanMichaelStephan | ||||
| 9 | JohnDoeV | DoeV,John | ||||
NameParse | ||||||
Edit: The function didn't come out so well above, it looks like:
Code:
=RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-MIN(SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{"Jr","III","IV","V","VI"},"")))),1))))&", "&SUBSTITUTE(A1," " &RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-MIN(SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{"Jr","III","IV","V","VI"},"")))),1)))),"")For A1 (single line obviously).
Upvote
0
