G
Guest
Is there a way to lip a name around in a single cell? For example Smith,
John to John Smith.
Thank you
Andrea
John to John Smith.
Thank you
Andrea
G
Guest
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)
=RIGHT(A1,LEN(A1)-(FIND(",",A1,1)+1))&" "&LEFT(A1,FIND(",",A1,1)-1)
R
Ron Rosenfeld
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
G
Guest
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
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
D
Dave Peterson
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
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
G
Guest
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
cell, this macro just doubles the text.........if there are two commas, some
text gets deleted.....
Vaya con Dios,
Chuck, CABGx3
D
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.)
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.)
R
Ron Rosenfeld
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
D
Dave Peterson
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.)
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.)
R
Ron Rosenfeld
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
D
Dave Peterson
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>.
used.
One returns a 0 base array and the other a 1 based array.
I blame CLR for the confusion <vvbg>.
R
Ron Rosenfeld
Yes, he should definitely upgrade from XL97!
--ron
G
Guest
..............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