Does anyone have a routine that can take a list of names entered as
"Lastname, Firstname" and change those names to "Firstname Lastname" within
the same cells?

Thanks,

--
tj

How robust does it need to be

Dim cell as Range, sStr as String
for each cell in Selection
v = Application.Split(cell,",")
sStr = Trim( trim(v(Ubound(v))) & " " & trim(v(Lbound(v))))
cell.Value = sStr
Next

--
Regards,
Tom Ogilvy

"tjtjjtjt" wrote in message
...
Does anyone have a routine that can take a list of names entered as
"Lastname, Firstname" and change those names to "Firstname Lastname"
within
the same cells?

Thanks,

--
tj

Take a look at
http://www.vbaexpress.com/forum/show...hlight=brettdj



--
HTH

Bob Phillips

"tjtjjtjt" wrote in message
...
Does anyone have a routine that can take a list of names entered as
"Lastname, Firstname" and change those names to "Firstname Lastname"
within
the same cells?

Thanks,

--
tj

Try the following:

Dim Rng As Range
Dim S As String
Dim Pos As Integer
For Each Rng In Range("A1:A10")
S = Rng.Text
Pos = InStr(1, S, ",")
If Pos 0 Then
Rng.Value = Mid(S, Pos + 2) & " " & Left(S, Pos - 1)
End If
Next Rng




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"tjtjjtjt" wrote in message
...
Does anyone have a routine that can take a list of names
entered as
"Lastname, Firstname" and change those names to "Firstname
Lastname" within
the same cells?

Thanks,

--
tj

I got a run time error (438: Object doesn't support this property of method)
on the line Application.Split.

I'll ultimately want to be able to identify the column of names within a
list of information and then switch the names.
The names are usually, but not always, in Column A, so the code will need to
look for one of two words, "Name" or "Instructor," and then switch the names
in every record.

--
tj


"Tom Ogilvy" wrote:

How robust does it need to be

Dim cell as Range, sStr as String
for each cell in Selection
v = Application.Split(cell,",")
sStr = Trim( trim(v(Ubound(v))) & " " & trim(v(Lbound(v))))
cell.Value = sStr
Next

--
Regards,
Tom Ogilvy

"tjtjjtjt" wrote in message
...
Does anyone have a routine that can take a list of names entered as
"Lastname, Firstname" and change those names to "Firstname Lastname"
within
the same cells?

Thanks,

--
tj

For the OP.
Lastname, firstname is already delimited by the comma, so the machinations
of the link are unnecessary. No mention was made of middle names or such.

--
Regards,
Tom Ogilvy



"Bob Phillips" wrote in message
...
Take a look at
http://www.vbaexpress.com/forum/show...hlight=brettdj



--
HTH

Bob Phillips

"tjtjjtjt" wrote in message
...
Does anyone have a routine that can take a list of names entered as
"Lastname, Firstname" and change those names to "Firstname Lastname"
within
the same cells?

Thanks,

--
tj

My typo. Application.split should just be Split.

This worked fine for me (xl2003)

Sub abc()
Dim cell As Range, sStr As String
For Each cell In Selection
v = Split(cell, ",")
sStr = Trim(Trim(v(UBound(v))) & " " & Trim(v(LBound(v))))
cell.Value = sStr
Next

End Sub


Note that split is not provided in xl97 and earlier.

--
Regards,
Tom Ogilvy

"tjtjjtjt" wrote in message
...
I got a run time error (438: Object doesn't support this property of
method)
on the line Application.Split.

I'll ultimately want to be able to identify the column of names within a
list of information and then switch the names.
The names are usually, but not always, in Column A, so the code will need
to
look for one of two words, "Name" or "Instructor," and then switch the
names
in every record.

--
tj


"Tom Ogilvy" wrote:

How robust does it need to be

Dim cell as Range, sStr as String
for each cell in Selection
v = Application.Split(cell,",")
sStr = Trim( trim(v(Ubound(v))) & " " & trim(v(Lbound(v))))
cell.Value = sStr
Next

--
Regards,
Tom Ogilvy

"tjtjjtjt" wrote in message
...
Does anyone have a routine that can take a list of names entered as
"Lastname, Firstname" and change those names to "Firstname Lastname"
within
the same cells?

Thanks,

--
tj

That did it. Thanks.
--
tj


"Tom Ogilvy" wrote:

My typo. Application.split should just be Split.

This worked fine for me (xl2003)

Sub abc()
Dim cell As Range, sStr As String
For Each cell In Selection
v = Split(cell, ",")
sStr = Trim(Trim(v(UBound(v))) & " " & Trim(v(LBound(v))))
cell.Value = sStr
Next

End Sub


Note that split is not provided in xl97 and earlier.

--
Regards,
Tom Ogilvy

"tjtjjtjt" wrote in message
...
I got a run time error (438: Object doesn't support this property of
method)
on the line Application.Split.

I'll ultimately want to be able to identify the column of names within a
list of information and then switch the names.
The names are usually, but not always, in Column A, so the code will need
to
look for one of two words, "Name" or "Instructor," and then switch the
names
in every record.

--
tj


"Tom Ogilvy" wrote:

How robust does it need to be

Dim cell as Range, sStr as String
for each cell in Selection
v = Application.Split(cell,",")
sStr = Trim( trim(v(Ubound(v))) & " " & trim(v(Lbound(v))))
cell.Value = sStr
Next

--
Regards,
Tom Ogilvy

"tjtjjtjt" wrote in message
...
Does anyone have a routine that can take a list of names entered as
"Lastname, Firstname" and change those names to "Firstname Lastname"
within
the same cells?

Thanks,

--
tj

Thanks, Chip.
--
tj


"Chip Pearson" wrote:

Try the following:

Dim Rng As Range
Dim S As String
Dim Pos As Integer
For Each Rng In Range("A1:A10")
S = Rng.Text
Pos = InStr(1, S, ",")
If Pos 0 Then
Rng.Value = Mid(S, Pos + 2) & " " & Left(S, Pos - 1)
End If
Next Rng




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"tjtjjtjt" wrote in message
...
Does anyone have a routine that can take a list of names
entered as
"Lastname, Firstname" and change those names to "Firstname
Lastname" within
the same cells?

Thanks,

--
tj

Thanks, Bob. Always nice to see different ways to do things.
--
tj


"Bob Phillips" wrote:

Take a look at
http://www.vbaexpress.com/forum/show...hlight=brettdj



--
HTH

Bob Phillips

"tjtjjtjt" wrote in message
...
Does anyone have a routine that can take a list of names entered as
"Lastname, Firstname" and change those names to "Firstname Lastname"
within
the same cells?

Thanks,

--
tj