last name first, first name last -- is there a code to combine 2 cells with last and first names?

[JasonK]

Thanks again for all your help. This group has been quite valuable to
me.

There are a couple of things I would like to do here.

First, I have two columns of data. Col B is last names, Col C is first
names. I would like to combine all that data into Col B with the Last
Name First, (comma space) then First Name.

Is there a quick macro I can write to do that?

Then, after the data is in that format, is there a way to reverse
column B so that the data is First Name First (space) Last Name (no
comma)?

That would be the best. I need the data both ways at different times
depending on what I'm doing.

Thanks again in advance for the advice.

JasonK

 

[Stefi]

If you need both formats, why don'tyou use two helper columns, say D with
formula
=B2&", "&C2
and E with formula
=C2&" "&B2
filled down as necessary?

Regards,
Stefi

„JasonK†ezt írta:

 

[JasonK]

because of other macros that run, i need the data to stay in that
column. i just need it last name, first name sometimes and
first name last name others.

is there a way to do it?

thanks again

JasonK

 

[Stefi]

If you run such a macro, it'll overwrite last names in column B. Do you want
to restore the original content (last names only) of column B?
Stefi

„JasonK†ezt írta:

 

[Patrick Molloy]

so B will be LstName, FirstName

Option Explicit

Sub CreateFullNames()
Dim source As Range
Set source = Range("B2")
Do Until source = ""
source.Value = source.Value & ", " & source.Offset(, 1).Value
Set source = source.Offset(1)
Loop
End Sub
Sub SetCtoFirstName()
Dim lastrow As Long
lastrow = Range("B2").End(xlDown).Row
Range(Range("C2"), Cells(lastrow, "C")).FormulaR1C1 = _
"=MID(RC2,FIND("","",RC2)+2,99)"
End Sub
Sub SetCtoLastName()
Dim lastrow As Long
lastrow = Range("B2").End(xlDown).Row
Range(Range("C2"), Cells(lastrow, "C")).FormulaR1C1 = _
"=LEFT(RC2,FIND("","",RC2)-1)"
End Sub

 

[Patrick Molloy]

.... so run CreateFullNames ONCE to merge B and C into B
then run SetCtoFirstName or SetCtoLastName ant anytime --- these just split
out the first or last name into C as you need

 

[JasonK]

Thank you Patrick. I will copy these and run them.

Thanks again for your help.

JasonK