Replace Certain Spaces

  • Thread starter Thread starter Greg Rivet
  • Start date Start date
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
 
One way:

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

HTH
Jason
Atlanta, GA
 
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
 
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
 
JR, non breaking space is CHAR(160), but macro errors out on If x <
comma....................TIA

Greg
 
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.)
 
And you can drop the
dim ictr as long
line
(used an existing macro and forgot to delete it.)
 
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
 
Back
Top