Text String with two Capitals trying to separate into two names

DreamWeaver1108

New Member
Joined
Apr 27, 2018
Messages
3
I have a text string, that is someone's name, that I need to separate into first and last name. No spaces or commas between them and want to separate them into first and last name. Here are a few examples"

BillWood
PeteSmith
JenniferBillings
AlexandraJones

How do I get those into first and last name?

Thank you!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Found this online... tried and works perfectly for this

Code:
Function SplitWords(ByVal Str As String) As String
'updateby Extendoffice 20151128
    Dim I As Integer
    SplitWords = Left(Str, 1)
    For I = 2 To Len(Trim(Str))
        If (Asc(Mid(Str, I, 1)) > 64) And _
           (Asc(Mid(Str, I, 1)) < 91) And _
           (Mid(Str, I - 1, 1) <> " ") Then _
            SplitWords = SplitWords & " "
        SplitWords = SplitWords & Mid(Str, I, 1)
    Next
End Function

then assuming your names begin in cell A2 in cell B2 input this formula
=SplitWords(A2)
 
Upvote 0
A few more options:

ABC
1
2BillWoodBillWood
3PeteSmithPeteSmith
4JenniferBillingsJenniferBillings
5AlexandraJonesAlexandraJones

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
C2=MID(A2,LEN(B2)+1,LEN(A2))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
B2{=LEFT(A2,MIN(IF(CODE(MID(A2&"A",ROW(INDIRECT("2:"&LEN(A2)+1)),1))<97,ROW(INDIRECT("1:"&LEN(A2))))))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



A similar formula to crazydragon84's. Two actually, for first and last names.

Another option is the often overlooked Flash Fill tool. Start with the names in A2:A5 (or wherever). In B2 put "Bill Wood". Now select B2 and press Control-E. The rest of the column will magically appear! Or you could put "Bill" in B2 and "Wood" in C2, then press Ctrl-E from B2 and again from C2. Really slick.
 
Last edited:
Upvote 0
sorry i interpreted separate first and last name as inserting a space haha. but you can still do that then use delimit function to split into first and last name.
 
Upvote 0
Eric, never knew about Flash Fill Tool. That's pretty cool. not sure how I can use it but i'm sure it can be useful for something haha.
 
Upvote 0
Maybe...


A
B
1
Names​
Formula​
2
BillWood​
Bill Wood​
3
PeteSmith​
Pete Smith​
4
JenniferBillings​
Jennifer Billings​
5
AlexandraJones​
Alexandra Jones​

Array formula in B2 copied down
=REPLACE(A2,SMALL(IFERROR(FIND(LEFT(ADDRESS(1,COLUMN($A$1:$Z$1),4)),A2),""),2),," ")
confirmed with Ctrl+Shift+Enter, not just Enter

M.
 
Upvote 0
I like the formula Marcelo, but what if the name is

JohnJones

?

Good catch!

New version

A
B
1
Names​
Formula​
2
BillWood​
Bill Wood​
3
PeteSmith​
Pete Smith​
4
JenniferBillings​
Jennifer Billings​
5
AlexandraJones​
Alexandra Jones​
6
JohnJones​
John Jones​
7

Array formula in B2
=REPLACE(A2,1+MIN(IFERROR(FIND(LEFT(ADDRESS(1,COLUMN($A$1:$Z$1),4)),MID(A2,2,LEN(A2))),"")),," ")
Ctrl+Shift+Enter

M.
 
Upvote 0
If you need first and last name in different columns...


A
B
C
1
Names​
First​
Last​
2
BillWood​
Bill​
Wood​
3
PeteSmith​
Pete​
Smith​
4
JenniferBillings​
Jennifer​
Billings​
5
AlexandraJones​
Alexandra​
Jones​
6
JohnJones​
John​
Jones​

Formula in B2 copied down
=LEFT(A2,LOOKUP(9.99E+307,FIND(LEFT(ADDRESS(1,COLUMN($A$1:$Z$1),4)),MID(A2,2,LEN(A2)))))

Formula in C2 copied down
=SUBSTITUTE(A2,B2,"",1)

M.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top