Replace Certain Spaces

G

Greg Rivet

I have a list of names such as

Green, Tom
White, Bill
Du Red, Mary
Blue, Peggy Sue
I want to loop through the names list and replace the space between double
names with a nonbreaking space and leave the space after the ",". TIA

Greg
 
J

Jason Morin

One way:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,", ","##"),CHAR
(32),CHAR(160)),"##",", ")

HTH
Jason
Atlanta, GA
 
G

Greg Rivet

Jason, appreciate the formula. Now how do I incorporate into a macro. TIA

Greg
 
J

Jason Morin

If you're looking for a written-from-scratch VBA macro,
I'm not the guy. If I needed to put this into a macro, I'd
record a macro that uses this formula, fills down the
column, and copy/paste special > value over the original
data.

Probably not the most efficient way, but it should work.

HTH
Jason
Atlanta, GA
 
J

JR

Try this non-elaborate way - (select your list first)
Macro:

Sub removespace()
Dim r As Range
Dim hold As String
Dim comma, x As Integer
Dim c As Variant
Set r = Selection
BrkSpc = "-" ' change this to whatever breaking space is
For Each c In r
hold = c.Value
comma = InStr(1, c, ",")
x = InStr(1, c, " ")
If x < comma Then c.Value = Left(hold, x - 1) &
BrkSpc & Right(hold, Len(hold) - x)
Next c
End Sub
 
G

Greg Rivet

JR, non breaking space is CHAR(160), but macro errors out on If x <
comma....................TIA

Greg
 
D

Dave Peterson

But I could steal Jason's idea and wrap it in code:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Selection has no Text Constants"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
.Value = Application.Substitute(Application.Substitute _
(Application.Substitute(.Value, ", ", "##"), _
Chr(32), Chr(160)), "##", ", ")
End With
Next myCell

End Sub

And if you're running xl2k or higher, you can replace each
application.substitute with Replace. (Replace was added in xl2k.)
 
D

Dave Peterson

And you can drop the
dim ictr as long
line
(used an existing macro and forgot to delete it.)
 
J

JR

Don't let this line break with "if x.."
should be all on one line. (It appears to have
wrapped in the response)

If x < comma Then c.Value = Left(hold, x - 1) &
BrkSpc & Right(hold, Len(hold) - x)

JR
 
Top