Change Smith, J to J Smith in a cell?

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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)
 
Is there a way to lip a name around in a single cell? For example Smith,
John to John Smith.

Thank you

Andrea
Click to expand...

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
 
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
 
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
 
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.)
 
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
Click to expand...

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.)
 
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.)
Click to expand...

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>.
 
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>.
Click to expand...

Yes, he should definitely upgrade from XL97!

--ron
 
Yes, he should definitely upgrade from XL97!
Click to expand...


..............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
 
Back
Top