Is there a way to switch name order in an Excel column?

[makocako]

I want to be able to switch from first_name last_ name to last_name,
first_name in an excel column without using text to columns then
concatenating the new columns. Is there a formula that will take care of
this more easily?

[BenjieLop]

I want to be able to switch from first_name last_ name to last_name,
first_name in an excel column without using text to columns then
concatenating the new columns. Is there a formula that will take care of
this more easily?

Assuming cell A1 is in this format "First Last" ...

Enter in cell B1 (for last name): =right(A1,len(A1)-find(" ",A1))

Enter in cell C1 (for first name): =left(A1,find(" ",A1)-1)

Hope this is what you are looking for.

Regards.

[AlfD]

Hi!

On the simple basic principle that Excel worksheet functions won't as a general rule change the data in the cell where you enter it/have it (other than formatting) you have to step outside that cell to do what you are seeking to do. (If they do change the cell they are in, you are into circular references which is, as they say, a-whole-nother ball game).

You can either step into helper cells/columns (which BenjieLop uses, though I would hope to use text to columns to do it) or you use VBA. Do I read you right that you want to avoid this "static" sort of solution and are seeking a dynamic one where you type in the name and it "changes" to your preferred format?

If you pursue VBA, then you will end up, in effect, programming something very like BenjieLop's solution.

How clean is your data? Is it guaranteed,for example, that every item is constructed as firstname<space>lastname? No middle names or initials: no extra spaces by accident?

Alf

[Bob Umlas, Excel MVP]

=mid(a1,find(" ",a1)+1,255)&", "&left(a1,find(" ",a1)-1)
then fill down

"makocako" wrote:

> I want to be able to switch from first_name last_ name to last_name,
> first_name in an excel column without using text to columns then
> concatenating the new columns. Is there a formula that will take care of
> this more easily?