format text

V

VDan

Hi,
I have a list of names in excel that is currently listed Firstname Lastname.
I need it to be listed Lastname, Firstname.
Is there a way to do this?
 
D

David Hepner

Try this:

=MID(C7,FIND(" ",C7,1)+1,LEN(C7)-FIND(" ",C7,1)) & ", " & LEFT(C7,FIND("
",C7,1))
 
M

Michael Hesse

If the First, Last is in a single cell, you need to break it up first. Here
are the steps:

1) Find the location of the comma with the Find function. If the text is
in A1, B1 could be =FIND(",",A1)

2) Then use this value to get the first name. C1 would be =LEFT(A1, B1-1_

3) Use the position of the comma to get the last name. D1 would be
=MID(A1, B1+1, 99999)

4) Now combine the last and first name. E1 would be =CONCATENATE(D1,
",",C1)

Of course you could combine all these steps in a single cell, that that gets
tricky.

Once you are done, you can Copy, Paste Special (Values), to save your
results and delete the columns used to make the conversion.

Hope this helps.
 
B

B. R.Ramachandran

Hi,

For a name in A1, try this formula in B1 (or C1, D1, ...)

=RIGHT(A1,LEN(A1)-FIND(" ",A1))&", "&LEFT(A1,FIND(" ",A1)-1)

The formula should work if there are no spaces within the firstname.

Regards,
B. R. Ramachandran
 
D

David Hepner

You need to change all of the C7's in the formula to the cell that has the
name.
 
V

VDan

Perhaps my original question was unclear.
In cell A1 the name as currently written is Microsoft Excel. I need to have
the name as Excel, Microsoft.
I have tried the suggested formulas but keep getting the #VALUE! error.
 
D

David Hepner

Is there a space between the the first and last name in your data?
For example: Bill Gates (in cell C7) should return Gates, Bill.
 
B

BenjieLop

VDan said:
Hi,
I have a list of names in excel that is currently listed Firstnam
Lastname.
I need it to be listed Lastname, Firstname.
Is there a way to do this?
Click to expand...

One way ...

Assuming your first names are listed in Column A (starting in Cell A1
and your last names are in Column B (starting in Cell B1), enter thi
formula

*=B1&", "&A1*

in Cell C1 and copy down until your range requirement is met.

Regards
 
B

B. R.Ramachandran

Hi VDan,

I did test the formula before posting my earliner reply.
"Microsoft Excel" in A1 does transform into "Excel, Microsoft".

Maybe, there is a formatting problem in the spreadsheet. Format them as
Text and see whether it helps.

B. R. Ramachandran
 
B

B. R.Ramachandran

Hi VDan,
Thanks for your quick reply. I was curious to know what was causing the
error.
Regards,
B. R. Ramachandran
 
V

VDan

I was away for a week and someone else did the data entry and changed all the
formats. At least now I know how to straighten it out.
Thanks for your help.
 
Top