Hello.
I need to convert a column of cells with names that look like this: SMITH John
to this: J SMITH
Previously I've been using Text to Columns, then =CONCATENATE(LEFT(B1,1)," ",A1)
I'd like to do this without using text to columns though, is this possible?
Last edited by Smally; 07-31-2014 at 10:53 AM.
Hi,
From your one example I presume that middle names are not a feature of your set-up then?
Regards
There are only a couple of name that have middle names in them. But because there's so few, I don't mind editing them myself
You can use the following formula for names without any middle name:
=MID(A1,SEARCH(" ",A1,1)+1,1)&" "&LEFT(A1,SEARCH(" ",A1,1)-1)
Thanks. It works brilliantly
Btw, what that method called where you've combined cells and text. I've always used concatenate myself
A small variation to the formula suggested by cbatrody to make it work for even if there is a middle name also.....
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,MID(A1,SEARCH(" ",A1,1)+1,1)&" "&LEFT(A1,SEARCH(" ",A1,1)-1),MID(A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1,1)&" "&LEFT(A1,SEARCH(" ",A1,1)-1))
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Or alternatively you may also try this......
=LEFT(REPLACE(A1,1,IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,FIND(" ",A1),FIND("*",SUBSTITUTE(A1," ","*",2))),""),1)&" "&LEFT(A1,FIND(" ",A1)-1)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks