Rearrangement and initials.

[Ocasio27]

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.

[#2 · Accepted solution]

Hi@Ocasio27 

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

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more

[#3 · Accepted solution]

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more

[#4 · Accepted solution]

If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more

[#5]

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

[#6]

@Ocasio27 

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.

[#7]

@mathetes 

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.

[#8]

@Ocasio27 

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.

[#9]

Hi Faraz,

What would be the formula to do the opposite, for example convert John Smith to Smith, John?

Thank you

[#10]

hi @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

[#11]

Thank you Faraz. I appreciate your help.