software

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 quot;Smith, Johnquot; is in cell A1

=RIGHT(A1,LEN(A1)-(FIND(quot;,quot;,A1,1) 1))amp;quot; quot;amp;LEFT(A1,FIND(quot;,quot;,A1,1)-1)
--
Kevin Backmannquot;Andreaquot; wrote:

gt; Is there a way to lip a name around in a single cell? For example Smith,
gt; John to John Smith.
gt;
gt; Thank you
gt;
gt; Andrea

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

gt;Is there a way to lip a name around in a single cell? For example Smith,
gt;John to John Smith.
gt;
gt;Thank you
gt;
gt;Andrea

This macro will look for a comma, and, if present, return the string after the
comma, a lt;spacegt;, then the string before the comma.

lt;alt-F11gt; 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 lt;alt-F8gt; and select the macro from the
dialog box that opens and lt;rungt;.

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

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

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

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

Vaya con Dios,
Chuck, CABGx3
quot;Ron Rosenfeldquot; wrote:

gt; On Tue, 14 Feb 2006 08:56:36 -0800, Andrea gt;
gt; wrote:
gt;
gt; gt;Is there a way to lip a name around in a single cell? For example Smith,
gt; gt;John to John Smith.
gt; gt;
gt; gt;Thank you
gt; gt;
gt; gt;Andrea
gt;
gt; This macro will look for a comma, and, if present, return the string after the
gt; comma, a lt;spacegt;, then the string before the comma.
gt;
gt; lt;alt-F11gt; opens the VB Editor.
gt;
gt; Ensure your project is highlighted in the project explorer window, then
gt; Insert/Module and paste the code below into the window that opens.
gt;
gt; Select a cell, or range of cells. Then lt;alt-F8gt; and select the macro from the
gt; dialog box that opens and lt;rungt;.
gt;
gt; ===================
gt; Sub flip()
gt; Dim temp() As String
gt; Dim rg As Range
gt;
gt; On Error Resume Next
gt; For Each rg In Selection
gt; temp = Split(rg.Text, quot;,quot;)
gt; rg.Value = temp(1) amp; quot; quot; amp; temp(0)
gt; rg.Value = Trim(rg.Text)
gt; Next rg
gt;
gt; End Sub
gt; =================
gt; --ron
gt;

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, quot;,quot;)
rg.Value = temp(UBound(temp)) amp; quot; quot; amp; temp(LBound(temp))
rg.Value = Trim(rg.Text)
Next rg
on Error goto 0

End SubFunction Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate(quot;{quot;quot;quot; amp; _
Application.Substitute(sStr, sdelim, quot;quot;quot;,quot;quot;quot;) amp; quot;quot;quot;}quot;)
End FunctionCLR wrote:
gt;
gt; Hi Ron........
gt; I'm using XL97 and I get a quot;Compile error...Sub or function not definedquot;
gt; error message, highlighting the word quot;Splitquot;
gt; ....need I set some reference or something?
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt; quot;Ron Rosenfeldquot; wrote:
gt;
gt; gt; On Tue, 14 Feb 2006 08:56:36 -0800, Andrea gt;
gt; gt; wrote:
gt; gt;
gt; gt; gt;Is there a way to lip a name around in a single cell? For example Smith,
gt; gt; gt;John to John Smith.
gt; gt; gt;
gt; gt; gt;Thank you
gt; gt; gt;
gt; gt; gt;Andrea
gt; gt;
gt; gt; This macro will look for a comma, and, if present, return the string after the
gt; gt; comma, a lt;spacegt;, then the string before the comma.
gt; gt;
gt; gt; lt;alt-F11gt; opens the VB Editor.
gt; gt;
gt; gt; Ensure your project is highlighted in the project explorer window, then
gt; gt; Insert/Module and paste the code below into the window that opens.
gt; gt;
gt; gt; Select a cell, or range of cells. Then lt;alt-F8gt; and select the macro from the
gt; gt; dialog box that opens and lt;rungt;.
gt; gt;
gt; gt; ===================
gt; gt; Sub flip()
gt; gt; Dim temp() As String
gt; gt; Dim rg As Range
gt; gt;
gt; gt; On Error Resume Next
gt; gt; For Each rg In Selection
gt; gt; temp = Split(rg.Text, quot;,quot;)
gt; gt; rg.Value = temp(1) amp; quot; quot; amp; temp(0)
gt; gt; rg.Value = Trim(rg.Text)
gt; gt; Next rg
gt; gt;
gt; gt; End Sub
gt; gt; =================
gt; gt; --ron
gt; gt;

--

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

quot;Dave Petersonquot; wrote:

gt; split was added in xl2k.
gt;
gt; Option Explicit
gt; Sub flip2()
gt; Dim temp As Variant
gt; Dim rg As Range
gt;
gt; On Error Resume Next
gt; For Each rg In Selection
gt; temp = Split97(rg.Text, quot;,quot;)
gt; rg.Value = temp(UBound(temp)) amp; quot; quot; amp; temp(LBound(temp))
gt; rg.Value = Trim(rg.Text)
gt; Next rg
gt; on Error goto 0
gt;
gt; End Sub
gt;
gt;
gt; Function Split97(sStr As String, sdelim As String) As Variant
gt; 'from Tom Ogilvy
gt; Split97 = Evaluate(quot;{quot;quot;quot; amp; _
gt; Application.Substitute(sStr, sdelim, quot;quot;quot;,quot;quot;quot;) amp; quot;quot;quot;}quot;)
gt; End Function
gt;
gt;
gt;
gt;
gt;
gt; CLR wrote:
gt; gt;
gt; gt; Hi Ron........
gt; gt; I'm using XL97 and I get a quot;Compile error...Sub or function not definedquot;
gt; gt; error message, highlighting the word quot;Splitquot;
gt; gt; ....need I set some reference or something?
gt; gt;
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt; quot;Ron Rosenfeldquot; wrote:
gt; gt;
gt; gt; gt; On Tue, 14 Feb 2006 08:56:36 -0800, Andrea gt;
gt; gt; gt; wrote:
gt; gt; gt;
gt; gt; gt; gt;Is there a way to lip a name around in a single cell? For example Smith,
gt; gt; gt; gt;John to John Smith.
gt; gt; gt; gt;
gt; gt; gt; gt;Thank you
gt; gt; gt; gt;
gt; gt; gt; gt;Andrea
gt; gt; gt;
gt; gt; gt; This macro will look for a comma, and, if present, return the string after the
gt; gt; gt; comma, a lt;spacegt;, then the string before the comma.
gt; gt; gt;
gt; gt; gt; lt;alt-F11gt; opens the VB Editor.
gt; gt; gt;
gt; gt; gt; Ensure your project is highlighted in the project explorer window, then
gt; gt; gt; Insert/Module and paste the code below into the window that opens.
gt; gt; gt;
gt; gt; gt; Select a cell, or range of cells. Then lt;alt-F8gt; and select the macro from the
gt; gt; gt; dialog box that opens and lt;rungt;.
gt; gt; gt;
gt; gt; gt; ===================
gt; gt; gt; Sub flip()
gt; gt; gt; Dim temp() As String
gt; gt; gt; Dim rg As Range
gt; gt; gt;
gt; gt; gt; On Error Resume Next
gt; gt; gt; For Each rg In Selection
gt; gt; gt; temp = Split(rg.Text, quot;,quot;)
gt; gt; gt; rg.Value = temp(1) amp; quot; quot; amp; temp(0)
gt; gt; gt; rg.Value = Trim(rg.Text)
gt; gt; gt; Next rg
gt; gt; gt;
gt; gt; gt; End Sub
gt; gt; gt; =================
gt; gt; gt; --ron
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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, quot;,quot;, quot;quot;)) lt;gt; 1 Then
'do nothing
Else
temp = Split97(rg.Text, quot;,quot;)
rg.Value = temp(UBound(temp)) amp; quot; quot; amp; 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(quot;{quot;quot;quot; amp; _
Application.Substitute(sStr, sdelim, quot;quot;quot;,quot;quot;quot;) amp; quot;quot;quot;}quot;)
End Function

(xl2k added replace, but in xl97, application.substitute can be used.)CLR wrote:
gt;
gt; Interesting Dave, thanks..........but in my XL97, if there is no comma in the
gt; cell, this macro just doubles the text.........if there are two commas, some
gt; text gets deleted.....
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; split was added in xl2k.
gt; gt;
gt; gt; Option Explicit
gt; gt; Sub flip2()
gt; gt; Dim temp As Variant
gt; gt; Dim rg As Range
gt; gt;
gt; gt; On Error Resume Next
gt; gt; For Each rg In Selection
gt; gt; temp = Split97(rg.Text, quot;,quot;)
gt; gt; rg.Value = temp(UBound(temp)) amp; quot; quot; amp; temp(LBound(temp))
gt; gt; rg.Value = Trim(rg.Text)
gt; gt; Next rg
gt; gt; on Error goto 0
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt;
gt; gt; Function Split97(sStr As String, sdelim As String) As Variant
gt; gt; 'from Tom Ogilvy
gt; gt; Split97 = Evaluate(quot;{quot;quot;quot; amp; _
gt; gt; Application.Substitute(sStr, sdelim, quot;quot;quot;,quot;quot;quot;) amp; quot;quot;quot;}quot;)
gt; gt; End Function
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; CLR wrote:
gt; gt; gt;
gt; gt; gt; Hi Ron........
gt; gt; gt; I'm using XL97 and I get a quot;Compile error...Sub or function not definedquot;
gt; gt; gt; error message, highlighting the word quot;Splitquot;
gt; gt; gt; ....need I set some reference or something?
gt; gt; gt;
gt; gt; gt; Vaya con Dios,
gt; gt; gt; Chuck, CABGx3
gt; gt; gt;
gt; gt; gt; quot;Ron Rosenfeldquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; On Tue, 14 Feb 2006 08:56:36 -0800, Andrea gt;
gt; gt; gt; gt; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt;Is there a way to lip a name around in a single cell? For example Smith,
gt; gt; gt; gt; gt;John to John Smith.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;Thank you
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;Andrea
gt; gt; gt; gt;
gt; gt; gt; gt; This macro will look for a comma, and, if present, return the string after the
gt; gt; gt; gt; comma, a lt;spacegt;, then the string before the comma.
gt; gt; gt; gt;
gt; gt; gt; gt; lt;alt-F11gt; opens the VB Editor.
gt; gt; gt; gt;
gt; gt; gt; gt; Ensure your project is highlighted in the project explorer window, then
gt; gt; gt; gt; Insert/Module and paste the code below into the window that opens.
gt; gt; gt; gt;
gt; gt; gt; gt; Select a cell, or range of cells. Then lt;alt-F8gt; and select the macro from the
gt; gt; gt; gt; dialog box that opens and lt;rungt;.
gt; gt; gt; gt;
gt; gt; gt; gt; ===================
gt; gt; gt; gt; Sub flip()
gt; gt; gt; gt; Dim temp() As String
gt; gt; gt; gt; Dim rg As Range
gt; gt; gt; gt;
gt; gt; gt; gt; On Error Resume Next
gt; gt; gt; gt; For Each rg In Selection
gt; gt; gt; gt; temp = Split(rg.Text, quot;,quot;)
gt; gt; gt; gt; rg.Value = temp(1) amp; quot; quot; amp; temp(0)
gt; gt; gt; gt; rg.Value = Trim(rg.Text)
gt; gt; gt; gt; Next rg
gt; gt; gt; gt;
gt; gt; gt; gt; End Sub
gt; gt; gt; gt; =================
gt; gt; gt; gt; --ron
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

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

gt;Interesting Dave, thanks..........but in my XL97, if there is no comma in the
gt;cell, this macro just doubles the text.........if there are two commas, some
gt;text gets deleted.....
gt;
gt;Vaya con Dios,
gt;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 --gt; 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:
gt;
gt; On Tue, 14 Feb 2006 11:04:30 -0800, CLR gt; wrote:
gt;
gt; gt;Interesting Dave, thanks..........but in my XL97, if there is no comma in the
gt; gt;cell, this macro just doubles the text.........if there are two commas, some
gt; gt;text gets deleted.....
gt; gt;
gt; gt;Vaya con Dios,
gt; gt;Chuck, CABGx3
gt;
gt; My original version, if there is no comma, will leave the result unchanged,
gt; except for the effect of the Trim function.
gt;
gt; If there are double commas, it will ignore anything after the second comma.
gt;
gt; Doe, John L., Jr --gt; John L. Doe
gt;
gt; I suspect the different results you are getting are related to Dave's
gt; implementation of the Split function, which did not appear until VBA6.
gt;
gt; Best,
gt;
gt; --ron

--

Dave Peterson

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

gt;I removed the on error stuff and had to change the indexes.
gt;
gt;But I think it's better to check to see what's in the cell first (the last post
gt;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