Inserting Spaces in Excel 1

[gizmo1973]

I have a mailing list of clients to go out. The following data is in single cells, in one column of Excel

Mr AAA Smith *
Miss A A Smith
Ms ABC Jones *
Mr BCD Jones *
Mr A B Jones
Mr Bert Smith
Miss B C D Smith

Where there is no spaces between initials I need to insert a space, the ones with a star.
Where there are spaces/forenames I need it left as is.
Is this possible?
I've tried insert spaces etc but is inserts them everywhere!!!



Phil

"Then I’d say what’s the point of having rank if you can’t pull it?"

(DCI Jack Meadows)

 

[frainbreeze]

hello

so long as all the titles, first initial/name and last name start with a Capital letter, you can do this:

find & replace all spaces with nothing so that you get
Mr Bert Smith --> MrBertSmith

then go to
tools --> Macro --> visual basic editor

then when you're in the editor go to
insert --> module

paste this in the window that pops up

Public Function AddSpaces(rng As Range)
    Dim lngI As Long
     
    AddSpaces = Left(rng.Text, 1)
    For lngI = 2 To Len(rng.Text)
        If Mid(rng.Text, lngI, 1) >= "A" And _
           Mid(rng.Text, lngI, 1) <= "Z" Then
            AddSpaces = AddSpaces & " " & _
                            Mid(rng.Text, lngI, 1)
        Else
            AddSpaces = AddSpaces & Mid(rng.Text, lngI, 1)
        End If
    Next lngI
         
End Function

then go to File --> Close & return to Excel

now use the function as you would anything else
for example:

[ A1 ]
MrAAASmith

so in [ B2 ] you can use
=AddSpaces(A1)

and it should add spaces wherever there is a capital letter, and then you can just copy and paste this formula for the rest of the names

fraiNbreeze

 

[gizmo1973]

Fantastic, One Star coming your way

Phil

"Then I’d say what’s the point of having rank if you can’t pull it?"

(DCI Jack Meadows)

 

[byundt]

Here is a somewhat shorter function using the RegExp object to add spaces between adjacent upper case letters. This function should be installed in a regular module sheet. It is used with a worksheet formula like:
=AddSpaces("ABCWright")
It may also be used in VBA subs or functions

Function AddSpaces(str As String) As String
'Finds doubled upper case letters and adds space between them. ABCWright becomes A B C Wright
Dim RgExp As Object, objMatch As Object, objMatches As Object
Set RgExp = CreateObject("VBScript.RegExp")
RgExp.Global = True
RgExp.Pattern = "([A-Z])([A-Z])"
AddSpaces = RgExp.Replace(RgExp.Replace(str, "$1 $2"), "$1 $2")
End Function