Forum Discussion
Rearrangement and initials.
Hello, I am trying to modify a column that has a name order that is not the one I want as output.
Here is an example
How it looks
Connor Smith, John
how I want it
John Connor Smith
So I want the name at the begging and remove the comma. Also I would like another column with initials, in this case it would be JCS
Thanks in advance.
HiOcasio27
You can do the same with the help of flashfill and below is the formula version can you use it to change the order of your names.
Connor Smith, John > John Connor Smith
=RIGHT(A2,LEN(A2)-FIND(",",A2)-1)&" "&LEFT(A2,FIND(",",A2)-1)To get the initials use the below formula
John Connor Smith > JCS
=LEFT(B2,1)&MID(B2,FIND(" ",B2)+1,1)&MID(B2,FIND(" ",B2,FIND(" ",B2)+1)+1,1)Results
Attached is the sample file for your ready reference
8 Replies
To go one step further than the excellent responses you've already gotten--which did tell you how to resolve the presenting problem of having the names in the wrong sequence.
May i suggest that the deeper solution, the preventive action, is to always record names NOT all in one cell, but rather in separate colulmns FIRST, LAST, MIDDLE as well as PREFIX and SUFFIX if needed. I realize it's entirely possible that you got this from somebody else, in which case, please pass on that advice.
Why? Because when names are stored that way it's very easy to put them together for various purposes, with orders like "John Q. Smith, Esq." or "Smith, John Q" for name tags or alphabetical lists respectively. And it is easy using LEFT to strip off initials.
Same principle applies to addresses and other types of information that we often think of as blocks of information.....it's easy to assemble the smaller building blocks into a desired order; it's easier to sort or sift by one of the components... and it's just good database design.
No control, I can understand. I've been there at times. I do wonder, though, whether you might approach them in a Columbo fashion (do you remember that TV show, or are you too young?)--sort of a "Gee, I just learned this, and wonder if you have had problems ever...; somebody told me that if..... etc"
Anyway, in your position, if this is a recurring delivery that you receive, certainly save the formulas you got yesterday, and reuse them. I'd recommend that you start storing the names as I was suggesting. I've done this with other spreadsheets I've gotten from other people -- and sometimes I've sent them back "fixed" so they can see a better way. But I certainly understand that not everybody is receptive to that kind of feedback.
- Hello,
All you need is Flash Fill
Assuming you have
Connor Smith, John in cell A1
Luiz Adrian, Rand in cell A2
Ibrahim Sule, Amad in cell A3 and so on
In cell B1, type in John Connor Smith and press Enter
In cell B2, press CTRL + E
You will have Rand Luiz Adrian in B2, Amad Ibrahim Sule in B3 and so on
If this answers your question, kindly accept as the Best Response.
Regards HiOcasio27
You can do the same with the help of flashfill and below is the formula version can you use it to change the order of your names.
Connor Smith, John > John Connor Smith
=RIGHT(A2,LEN(A2)-FIND(",",A2)-1)&" "&LEFT(A2,FIND(",",A2)-1)To get the initials use the below formula
John Connor Smith > JCS
=LEFT(B2,1)&MID(B2,FIND(" ",B2)+1,1)&MID(B2,FIND(" ",B2,FIND(" ",B2)+1)+1,1)Results
Attached is the sample file for your ready reference
- Hi Faraz,
What would be the formula to do the opposite, for example convert John Smith to Smith, John?
Thank youhi Dinky58,
You slight need to play the formula & here is your requested version
=RIGHT(A2,LEN(A2)-FIND(" ",A2))&", "&LEFT(A2,FIND(" ",A2)-1)Regards, Faraz Shaikh | MVP, MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more
