multiple spaces between first and last name

  • Thread starter Thread starter bj
  • Start date Start date
B

bj

I have a column of names

firstname(spaces)last name

Problem is there are multiple spaces between the first and last name, They
vary. How do I reduce them to jsu the one space?

Usually I would use find and replace but it can vary between 2 and 10
spaces.

Thanks
Brett
 
This custom function should do the job. Copy/paste to a macro module
and use as a normal function eg. =ONESPACE(A1) and copy down.

'---------------------------------------------
Public Function ONESPACE(st As String)
n1 = ""
n2 = ""
sp = 0
'------------------------
For c = 1 To Len(st)
If Mid(st, c, 1) <> " " Then
If sp = 0 Then
n1 = n1 & Mid(st, c, 1)
Else
n2 = n2 & Mid(st, c, 1)
End If
Else
sp = 1
End If
Next
'-----------------------
ONESPACE = n1 & " " & n2
End Function
'-------------------------------------------------
 
This custom function should do the job. Copy/paste to a macro module
and use as a normal function eg. =ONESPACE(A1) and copy down.

'---------------------------------------------
Public Function ONESPACE(st As String)
Click to expand...
...

And the point of this rather than using the built-in function TRIM() is . . . ?

Reinventing the wheel is usually a bad idea.
 
Back
Top