format text

[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?

[Mike]

in the next column do
=concatenate(Lastnamecell,", ",Firstnamecell)

"VDan" wrote:

> 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?

[BenjieLop]

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?

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 this formula

=B1&", "&A1

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

Regards.

[VDan]

Both names are in the same cell.

"Mike" wrote:

> in the next column do
> =concatenate(Lastnamecell,", ",Firstnamecell)
>
> "VDan" wrote:
>
> > 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?

[David Hepner]

Try this:

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

"VDan" wrote:

> Both names are in the same cell.
>
> "Mike" wrote:
>
> > in the next column do
> > =concatenate(Lastnamecell,", ",Firstnamecell)
> >
> > "VDan" wrote:
> >
> > > 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?

[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.
"VDan" <[email protected]> wrote in message
news:[email protected]...
> 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?

[VDan]

excel responded #VALUE!

"David Hepner" wrote:

> Try this:
>
> =MID(C7,FIND(" ",C7,1)+1,LEN(C7)-FIND(" ",C7,1)) & ", " & LEFT(C7,FIND("
> ",C7,1))
>
> "VDan" wrote:
>
> > Both names are in the same cell.
> >
> > "Mike" wrote:
> >
> > > in the next column do
> > > =concatenate(Lastnamecell,", ",Firstnamecell)
> > >
> > > "VDan" wrote:
> > >
> > > > 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?

[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


"VDan" wrote:

> 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?

[David Hepner]

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

"VDan" wrote:

> excel responded #VALUE!
>
> "David Hepner" wrote:
>
> > Try this:
> >
> > =MID(C7,FIND(" ",C7,1)+1,LEN(C7)-FIND(" ",C7,1)) & ", " & LEFT(C7,FIND("
> > ",C7,1))
> >
> > "VDan" wrote:
> >
> > > Both names are in the same cell.
> > >
> > > "Mike" wrote:
> > >
> > > > in the next column do
> > > > =concatenate(Lastnamecell,", ",Firstnamecell)
> > > >
> > > > "VDan" wrote:
> > > >
> > > > > 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?

[VDan]

I did.

"David Hepner" wrote:

> You need to change all of the C7's in the formula to the cell that has the
> name.
>
> "VDan" wrote:
>
> > excel responded #VALUE!
> >
> > "David Hepner" wrote:
> >
> > > Try this:
> > >
> > > =MID(C7,FIND(" ",C7,1)+1,LEN(C7)-FIND(" ",C7,1)) & ", " & LEFT(C7,FIND("
> > > ",C7,1))
> > >
> > > "VDan" wrote:
> > >
> > > > Both names are in the same cell.
> > > >
> > > > "Mike" wrote:
> > > >
> > > > > in the next column do
> > > > > =concatenate(Lastnamecell,", ",Firstnamecell)
> > > > >
> > > > > "VDan" wrote:
> > > > >
> > > > > > 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?

[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.

"B. R.Ramachandran" wrote:

> 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
>
>
> "VDan" wrote:
>
> > 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?

[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.


"VDan" wrote:

> I did.
>
> "David Hepner" wrote:
>
> > You need to change all of the C7's in the formula to the cell that has the
> > name.
> >
> > "VDan" wrote:
> >
> > > excel responded #VALUE!
> > >
> > > "David Hepner" wrote:
> > >
> > > > Try this:
> > > >
> > > > =MID(C7,FIND(" ",C7,1)+1,LEN(C7)-FIND(" ",C7,1)) & ", " & LEFT(C7,FIND("
> > > > ",C7,1))
> > > >
> > > > "VDan" wrote:
> > > >
> > > > > Both names are in the same cell.
> > > > >
> > > > > "Mike" wrote:
> > > > >
> > > > > > in the next column do
> > > > > > =concatenate(Lastnamecell,", ",Firstnamecell)
> > > > > >
> > > > > > "VDan" wrote:
> > > > > >
> > > > > > > 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?

[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



"VDan" wrote:

> 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.
>
> "B. R.Ramachandran" wrote:
>
> > 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
> >
> >
> > "VDan" wrote:
> >
> > > 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?

[VDan]

It works once I formatted my column as text. A thousand thanks...

"B. R.Ramachandran" wrote:

> 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
>
>
>
> "VDan" wrote:
>
> > 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.
> >
> > "B. R.Ramachandran" wrote:
> >
> > > 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
> > >
> > >
> > > "VDan" wrote:
> > >
> > > > 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?

[B. R.Ramachandran]

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

"VDan" wrote:

> It works once I formatted my column as text. A thousand thanks...
>
> "B. R.Ramachandran" wrote:
>
> > 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
> >
> >
> >
> > "VDan" wrote:
> >
> > > 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.
> > >
> > > "B. R.Ramachandran" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "VDan" wrote:
> > > >
> > > > > 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?

[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.

"B. R.Ramachandran" wrote:

> Hi VDan,
> Thanks for your quick reply. I was curious to know what was causing the
> error.
> Regards,
> B. R. Ramachandran
>
> "VDan" wrote:
>
> > It works once I formatted my column as text. A thousand thanks...
> >
> > "B. R.Ramachandran" wrote:
> >
> > > 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
> > >
> > >
> > >
> > > "VDan" wrote:
> > >
> > > > 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.
> > > >
> > > > "B. R.Ramachandran" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
> > > > > "VDan" wrote:
> > > > >
> > > > > > 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?