Forum Discussion
Rearrangement and initials.
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
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.
Good idea thanks, unfortunately I receive these files from another department were I have no control at all and I have to fix them first then use them.
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.
