Insert space between first and last names

[Yippy_Kiyey]

Hello,

I need a formula or an easy way to separate the first and last names if the data looks like this.

JohnSmith
GeorgeBush
SimonCowell

Thank you much in advance

Rey

 

[Shawnyv]

Here's a macro that will do it.

Note: Ensure there are no capital letters to end a name.
Note: This macro assumes the names are in column A, with a title row on A1 and contiguous data starting on row 2.
Note: This is inefficient, but will do the trick.
Note: This assumes no data in the sheet other than the names in column A

Sub Name_Changer()
'
' Macro1 Macro
'

'
    ' Initialize Macro
    CAPCONSTANT = 10
    Range("B1").FormulaR1C1 = "Len"
    Range("B2").FormulaR1C1 = "=LEN(RC[-1])"
    Range("A1").End(xlDown).Offset(0, 1).Value = "d"
    Range("B2").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Range("C2").Select
    
    'Main loop
    Do While ActiveCell.Column <> 2
    nameLen = Range("B2").Value
    'Separates out each letter of name, then checks for upper casing.  If it
    'finds it, adds an empty cell
    For x = 1 To nameLen
        ActiveCell.FormulaR1C1 = "=MID(RC1," & x & ",1)"
        ActiveCell.Offset(0, 1).Select
    Next x
    ActiveCell.Offset(0, -1).Select
    ActiveCell.End(xlToLeft).Offset(0, 3).Select
    For x = 1 To nameLen
        If ActiveCell.Value = (UCase(ActiveCell.Value)) Then
            Range(ActiveCell, ActiveCell.Offset(0, nameLen)).Select
            Selection.Cut
            ActiveCell.Offset(0, 1).Select
            ActiveSheet.Paste
            ActiveCell.Offset(0, -1).Value = " "
            ActiveCell.Offset(0, 1).Select
        Else
            ActiveCell.Offset(0, 1).Select
        End If
        
    Next x
    ActiveCell.Offset(1, 0).End(xlToLeft).Offset(0, 1).Select
    
    Loop
    
    Range("C2").Select
    Do While ActiveCell.Value <> ""
        Name = ""
        nameLen = ActiveCell.Offset(0, -1).Value
        For x = 1 To (nameLen + CAPCONSTANT)
            Name = Name + ActiveCell.Value
            ActiveCell.Offset(0, 1).Select
        Next x
    ActiveCell.Offset(1, 0).Select
    xrow = ActiveCell.Row
    Cells(xrow, 3).Select
    Loop
    
    Cells(2, 3).Select
    Do While ActiveCell.Value <> ""
        Name = ""
        nameLen = ActiveCell.Offset(0, -1).Value
        For x = 1 To (nameLen + CAPCONSTANT)
            Name = Name + ActiveCell.Value
            ActiveCell.Offset(0, 1).Select
        Next x
    Cells(ActiveCell.Row, 1).Value = Name
    ActiveCell.Offset(1, 0).Select
    Cells(ActiveCell.Row, 3).Select
    Loop
    Range("B1:ZZ10000").ClearContents
    
End Sub

 

[vlady]

udf?

Function spaceincaps(txt As String) As String
    
With CreateObject("vbscript.regexp")
        .Pattern = "([A-Z])"
        .Global = True
                     spaceincaps = Trim(.Replace(txt, Chr(32) & "$1"))
   
End With

End Function

A1 = OscarDeLaHoya

=spaceincaps(A1)

Just a prob in DeLa is it with space ?

 

[mmodroger]

This CSE formula should do what you want
=REPLACE(A1, 1+MIN(FIND(CHAR(64+COLUMN($A$1:$Z$1)),MID(A1,2,255)&(CHAR(64+COLUMN($A$1:$Z$1))))), 0, " ")

It should be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

i used mikerickson's formula . In case if you want to use formulas

Excel Workbook
	A	B
1	BillyBobThornton	Billy Bob Thornton
2	MartinStLouis	Martin St Louis
3	OscarDeLaHoya	Oscar De LaHoya
Sheet3

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[mmodroger]

I just had a revelation.

You have a formula that will do JohnSmith to John Smith.
Put the formula in B1, drag it right to C1 and it will turn

BillyBobThornton > Billy BobThorton > Billy Bob Thorton

ANDDD another easier one use this concept. Mike is good!

Excel Workbook
	A	B
1	BillyBobThornton	Billy Bob Thornton
2	MartinStLouis	Martin St Louis
3	OscarDeLaHoya	Oscar De LaHoya
Sheet3

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[NewbieWonCanoobie]

How do you guys know these things?


Darn you're good.

 

[samaco]

This CSE formula should do what you want
=REPLACE(A1, 1+MIN(FIND(CHAR(64+COLUMN($A$1:$Z$1)),MID(A1,2,255)&(CHAR(64+COLUMN($A$1:$Z$1))))), 0, " ")

It should be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

I am facing the same issue.
I tested your forumla but it did not work. I clicked on ctril + shift + enter but nothing happen.
What shall I do please?

 

[Geo Jul]

i used mikerickson's formula . In case if you want to use formulas

Excel Workbook
	A	B
1	BillyBobThornton	Billy Bob Thornton
2	MartinStLouis	Martin St Louis
3	OscarDeLaHoya	Oscar De LaHoya
Sheet3

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

perfect solution