Is there a way to lip a name around in a single cell? For example Smith,
John to John Smith.

Thank you

Andrea

The following formula assumes that your value "Smith, John" is in cell A1

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


"Andrea" wrote:

Is there a way to lip a name around in a single cell? For example Smith,
John to John Smith.

Thank you

Andrea

On Tue, 14 Feb 2006 08:56:36 -0800, Andrea
wrote:

Is there a way to lip a name around in a single cell? For example Smith,
John to John Smith.

Thank you

Andrea

This macro will look for a comma, and, if present, return the string after the
comma, a <space, then the string before the comma.

<alt-F11 opens the VB Editor.

Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

Select a cell, or range of cells. Then <alt-F8 and select the macro from the
dialog box that opens and <run.

===================
Sub flip()
Dim temp() As String
Dim rg As Range

On Error Resume Next
For Each rg In Selection
temp = Split(rg.Text, ",")
rg.Value = temp(1) & " " & temp(0)
rg.Value = Trim(rg.Text)
Next rg

End Sub
=================
--ron

Hi Ron........
I'm using XL97 and I get a "Compile error...Sub or function not defined"
error message, highlighting the word "Split"
.....need I set some reference or something?

Vaya con Dios,
Chuck, CABGx3



"Ron Rosenfeld" wrote:

On Tue, 14 Feb 2006 08:56:36 -0800, Andrea
wrote:

Is there a way to lip a name around in a single cell? For example Smith,
John to John Smith.

Thank you

Andrea

This macro will look for a comma, and, if present, return the string after the
comma, a <space, then the string before the comma.

<alt-F11 opens the VB Editor.

Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

Select a cell, or range of cells. Then <alt-F8 and select the macro from the
dialog box that opens and <run.

===================
Sub flip()
Dim temp() As String
Dim rg As Range

On Error Resume Next
For Each rg In Selection
temp = Split(rg.Text, ",")
rg.Value = temp(1) & " " & temp(0)
rg.Value = Trim(rg.Text)
Next rg

End Sub
=================
--ron

split was added in xl2k.

Option Explicit
Sub flip2()
Dim temp As Variant
Dim rg As Range

On Error Resume Next
For Each rg In Selection
temp = Split97(rg.Text, ",")
rg.Value = temp(UBound(temp)) & " " & temp(LBound(temp))
rg.Value = Trim(rg.Text)
Next rg
on Error goto 0

End Sub


Function Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function





CLR wrote:

Hi Ron........
I'm using XL97 and I get a "Compile error...Sub or function not defined"
error message, highlighting the word "Split"
....need I set some reference or something?

Vaya con Dios,
Chuck, CABGx3

"Ron Rosenfeld" wrote:

On Tue, 14 Feb 2006 08:56:36 -0800, Andrea
wrote:

Is there a way to lip a name around in a single cell? For example Smith,
John to John Smith.

Thank you

Andrea

This macro will look for a comma, and, if present, return the string after the
comma, a <space, then the string before the comma.

<alt-F11 opens the VB Editor.

Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

Select a cell, or range of cells. Then <alt-F8 and select the macro from the
dialog box that opens and <run.

===================
Sub flip()
Dim temp() As String
Dim rg As Range

On Error Resume Next
For Each rg In Selection
temp = Split(rg.Text, ",")
rg.Value = temp(1) & " " & temp(0)
rg.Value = Trim(rg.Text)
Next rg

End Sub
=================
--ron


--

Dave Peterson

Interesting Dave, thanks..........but in my XL97, if there is no comma in the
cell, this macro just doubles the text.........if there are two commas, some
text gets deleted.....

Vaya con Dios,
Chuck, CABGx3




"Dave Peterson" wrote:

split was added in xl2k.

Option Explicit
Sub flip2()
Dim temp As Variant
Dim rg As Range

On Error Resume Next
For Each rg In Selection
temp = Split97(rg.Text, ",")
rg.Value = temp(UBound(temp)) & " " & temp(LBound(temp))
rg.Value = Trim(rg.Text)
Next rg
on Error goto 0

End Sub


Function Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function





CLR wrote:

Hi Ron........
I'm using XL97 and I get a "Compile error...Sub or function not defined"
error message, highlighting the word "Split"
....need I set some reference or something?

Vaya con Dios,
Chuck, CABGx3

"Ron Rosenfeld" wrote:

On Tue, 14 Feb 2006 08:56:36 -0800, Andrea
wrote:

Is there a way to lip a name around in a single cell? For example Smith,
John to John Smith.

Thank you

Andrea

This macro will look for a comma, and, if present, return the string after the
comma, a <space, then the string before the comma.

<alt-F11 opens the VB Editor.

Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

Select a cell, or range of cells. Then <alt-F8 and select the macro from the
dialog box that opens and <run.

===================
Sub flip()
Dim temp() As String
Dim rg As Range

On Error Resume Next
For Each rg In Selection
temp = Split(rg.Text, ",")
rg.Value = temp(1) & " " & temp(0)
rg.Value = Trim(rg.Text)
Next rg

End Sub
=================
--ron


--

Dave Peterson

What should happen if there is no comma?

What should happen if there are multiple commas?

A little validation before hand is probably a good thing:

Option Explicit
Sub flip3()
Dim temp As Variant
Dim rg As Range

For Each rg In Selection
If Len(rg.Text) _
- Len(Application.Substitute(rg.Text, ",", "")) < 1 Then
'do nothing
Else
temp = Split97(rg.Text, ",")
rg.Value = temp(UBound(temp)) & " " & temp(LBound(temp))
rg.Value = Trim(rg.Text)
End If
Next rg
End Sub

Function Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

(xl2k added replace, but in xl97, application.substitute can be used.)


CLR wrote:

Interesting Dave, thanks..........but in my XL97, if there is no comma in the
cell, this macro just doubles the text.........if there are two commas, some
text gets deleted.....

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote:

split was added in xl2k.

Option Explicit
Sub flip2()
Dim temp As Variant
Dim rg As Range

On Error Resume Next
For Each rg In Selection
temp = Split97(rg.Text, ",")
rg.Value = temp(UBound(temp)) & " " & temp(LBound(temp))
rg.Value = Trim(rg.Text)
Next rg
on Error goto 0

End Sub


Function Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function





CLR wrote:

Hi Ron........
I'm using XL97 and I get a "Compile error...Sub or function not defined"
error message, highlighting the word "Split"
....need I set some reference or something?

Vaya con Dios,
Chuck, CABGx3

"Ron Rosenfeld" wrote:

On Tue, 14 Feb 2006 08:56:36 -0800, Andrea
wrote:

Is there a way to lip a name around in a single cell? For example Smith,
John to John Smith.

Thank you

Andrea

This macro will look for a comma, and, if present, return the string after the
comma, a <space, then the string before the comma.

<alt-F11 opens the VB Editor.

Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

Select a cell, or range of cells. Then <alt-F8 and select the macro from the
dialog box that opens and <run.

===================
Sub flip()
Dim temp() As String
Dim rg As Range

On Error Resume Next
For Each rg In Selection
temp = Split(rg.Text, ",")
rg.Value = temp(1) & " " & temp(0)
rg.Value = Trim(rg.Text)
Next rg

End Sub
=================
--ron


--

Dave Peterson


--

Dave Peterson

On Tue, 14 Feb 2006 11:04:30 -0800, CLR wrote:

Interesting Dave, thanks..........but in my XL97, if there is no comma in the
cell, this macro just doubles the text.........if there are two commas, some
text gets deleted.....

Vaya con Dios,
Chuck, CABGx3

My original version, if there is no comma, will leave the result unchanged,
except for the effect of the Trim function.

If there are double commas, it will ignore anything after the second comma.

Doe, John L., Jr -- John L. Doe

I suspect the different results you are getting are related to Dave's
implementation of the Split function, which did not appear until VBA6.

Best,


--ron

I removed the on error stuff and had to change the indexes.

But I think it's better to check to see what's in the cell first (the last post
added a little check for a single comma.)

Ron Rosenfeld wrote:

On Tue, 14 Feb 2006 11:04:30 -0800, CLR wrote:

Interesting Dave, thanks..........but in my XL97, if there is no comma in the
cell, this macro just doubles the text.........if there are two commas, some
text gets deleted.....

Vaya con Dios,
Chuck, CABGx3

My original version, if there is no comma, will leave the result unchanged,
except for the effect of the Trim function.

If there are double commas, it will ignore anything after the second comma.

Doe, John L., Jr -- John L. Doe

I suspect the different results you are getting are related to Dave's
implementation of the Split function, which did not appear until VBA6.

Best,

--ron

--

Dave Peterson

On Tue, 14 Feb 2006 13:50:37 -0600, Dave Peterson
wrote:

I removed the on error stuff and had to change the indexes.

But I think it's better to check to see what's in the cell first (the last post
added a little check for a single comma.)

Now having a bit more time to look at it, I see the problem with the indices.
It seems we made somewhat different design decisions, though.

Mine does nothing if there are no commas because of the On Error stuff. When
it tries to access Temp(1) an error results, so that part is merely skipped.
So there is no need to check for less than one comma.

I also saw no need to use LBound and UBound as opposed to directly addressing
elements 0 and 1.

If there is more than one comma, mine returns the first and second elements in
reverse order, and ignores any subsequent elements. Should it do nothing if
there is more than one comma? Or should it return the 1st and 2nd elements
reversed? You and I made different decisions at that point.

Best,

--ron

The reason I used lbound and ubound was so that split and split97 could both be
used.

One returns a 0 base array and the other a 1 based array.

I blame CLR for the confusion <vvbg.

Ron Rosenfeld wrote:

On Tue, 14 Feb 2006 13:50:37 -0600, Dave Peterson
wrote:

I removed the on error stuff and had to change the indexes.

But I think it's better to check to see what's in the cell first (the last post
added a little check for a single comma.)

Now having a bit more time to look at it, I see the problem with the indices.
It seems we made somewhat different design decisions, though.

Mine does nothing if there are no commas because of the On Error stuff. When
it tries to access Temp(1) an error results, so that part is merely skipped.
So there is no need to check for less than one comma.

I also saw no need to use LBound and UBound as opposed to directly addressing
elements 0 and 1.

If there is more than one comma, mine returns the first and second elements in
reverse order, and ignores any subsequent elements. Should it do nothing if
there is more than one comma? Or should it return the 1st and 2nd elements
reversed? You and I made different decisions at that point.

Best,

--ron

--

Dave Peterson

On Tue, 14 Feb 2006 20:49:51 -0600, Dave Peterson
wrote:

The reason I used lbound and ubound was so that split and split97 could both be
used.

One returns a 0 base array and the other a 1 based array.

I blame CLR for the confusion <vvbg.

Yes, he should definitely upgrade from XL97!

--ron

I blame CLR for the confusion <vvbg

Yes, he should definitely upgrade from XL97!


..............LOL....(I'm sorry guys)
Believe me, I would love to get up off of XL97, I have 2k at home and I was
constantly experiencing the crossover grief. This client has about 1000
computers on XL97, so all my work for them must run on 97.....so naturally
that's what I work in for them. Besides the differences in VBA, there are
also some nifty Add-ins out there that won't work in 97 :(

Anyway, thanks to both you guys for all the help.........

Vaya con Dios,
Chuck, CABGx3




"Ron Rosenfeld" wrote:

On Tue, 14 Feb 2006 20:49:51 -0600, Dave Peterson
wrote:

The reason I used lbound and ubound was so that split and split97 could both be
used.

One returns a 0 base array and the other a 1 based array.

I blame CLR for the confusion <vvbg.

Yes, he should definitely upgrade from XL97!

--ron