Search/Replace multiple chars with multiple values

[tbcomputerguy]

I have been given a list like so;
stan smith joe wilson ken hanson mike jablonski

I would to have the list look like so:
stan smith | joe wilson | ken hanson | mike jablonski

the substitute value only works with a specific set value. so it would set the first delimited between smith and joe but will not carry on to the next set of values. this probably need some vba.

Dave

 

[JoeMo]

Select the range that holds your list, then run this macro:

Sub computerguy()
'select the range to be altered then run this macro
Dim c As Range, V As Variant, i As Long, x As String, ct As Long
For Each c In Selection
    If InStr(c.Value, " ") > 0 Then
        V = Split(c.Value, " ")
        For i = LBound(V) To UBound(V)
            ct = ct + 1
            If ct Mod 2 <> 0 Then
                x = x & V(i) & " "
            Else
                x = x & V(i) & " | "
            End If
        Next i
    Else
        GoTo Nx
    End If
    Application.ScreenUpdating = False
    c.Value = Mid(x, 1, Len(x) - 2)
Nx:    x = ""
Next c
Application.ScreenUpdating = True
End Sub

 

[tbcomputerguy]

Oh yes, forgot an important point. These lists sometime have anywhere from one to 30 entries..

 

[JoeMo]

See post #2 .

 

[tbcomputerguy]

That worked perfectly. I posted the last reply almost at the same time you posted yours.

 

[JoeMo]

That worked perfectly. I posted the last reply almost at the same time you posted yours.

There's a trailing space in what I gave you. To remove it change this line:

c.Value = Mid(x, 1, Len(x) - 2)

to this:

c.Value = Trim(Mid(x, 1, Len(x) - 2))