need a macro to edit names in excel
 

I'd like to create (or use someone else's) a macro to edit names in Excel
spreadsheets. I need a.) to remove the last name and comma from a
last-name-first listing, and b.) to remove the comma and first name from a
last-name-first listing. In a.) that would leave the cell with only the first
name remaining, and in b.) would leave only the last name remaining.

Anyone have any suggestions or available macro's?
thanks!
Betsy

need a macro to edit names in excel
 

Use Data Text To Columns with comma as the delimiter.

--
__________________________________
HTH

Bob

"Betsy" wrote in message
...
I'd like to create (or use someone else's) a macro to edit names in Excel
spreadsheets. I need a.) to remove the last name and comma from a
last-name-first listing, and b.) to remove the comma and first name from a
last-name-first listing. In a.) that would leave the cell with only the
first
name remaining, and in b.) would leave only the last name remaining.

Anyone have any suggestions or available macro's?
thanks!
Betsy

need a macro to edit names in excel
 

Below are the two macros I have used to do this and not have any spaces that
the text to columns leaves (unless there's a way to do the text to column
that can take out the space that I don't know about). You'll have to change
the start range, but should do the trick. Hope this helps!

Option Explicit
Sub RemoveLastName()

Dim r As String
Dim listname As Range

Set listname = Range("A14")
Do Until listname = ""
Do
If Left(listname, 1) < " " Then
listname = Right(listname, Len(listname) - 1)
Else:
listname = Right(listname, Len(listname) - 1)
Exit Do
End If
Loop
Set listname = listname.Offset(1, 0)
Loop

End Sub

Sub RemoveFirstName()

Dim r As String
Dim listname As Range

Set listname = Range("C14")
Do Until listname = ""
Do
If Right(listname, 1) < "," Then
listname = Left(listname, Len(listname) - 1)
Else:
listname = Left(listname, Len(listname) - 1)
Exit Do
End If
Loop
Set listname = listname.Offset(1, 0)
Loop

End Sub
--
-SA


"Betsy" wrote:

I'd like to create (or use someone else's) a macro to edit names in Excel
spreadsheets. I need a.) to remove the last name and comma from a
last-name-first listing, and b.) to remove the comma and first name from a
last-name-first listing. In a.) that would leave the cell with only the first
name remaining, and in b.) would leave only the last name remaining.

Anyone have any suggestions or available macro's?
thanks!
Betsy

need a macro to edit names in excel
 

Perfect. You just saved me countless hours!
Thanks so much !

"Bob Phillips" wrote:

Use Data Text To Columns with comma as the delimiter.

--
__________________________________
HTH

Bob

"Betsy" wrote in message
...
I'd like to create (or use someone else's) a macro to edit names in Excel
spreadsheets. I need a.) to remove the last name and comma from a
last-name-first listing, and b.) to remove the comma and first name from a
last-name-first listing. In a.) that would leave the cell with only the
first
name remaining, and in b.) would leave only the last name remaining.

Anyone have any suggestions or available macro's?
thanks!
Betsy