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 <(E-Mail Removed)>
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 <(E-Mail Removed)>
> 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 <(E-Mail Removed)>
> > 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 <(E-Mail Removed)>
> > > 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 <(E-Mail Removed)>
> > > > 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 <(E-Mail Removed)> 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 <(E-Mail Removed)> 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 <(E-Mail Removed)>
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