Separate cell text FirstnameLastname into two columns

[drewannie]

How can I separate a column of cells containing a single text string in
each[these are names formated as a string with no delimiter as in
"FirstLast" with the first letter of first and last name capitalized
and the rest lower case...] into two columns of text called Firstname
and Lastname?

Thanks!

 

[jetted]

Hi

To separate one column in 2. Assuming Column A contains Lastname,
firstname. Column B must be empty if not insert a column to create and
empty columnB.

Once completed click on column A (this should highlight Columns), from
there go to menu ---->Data ---->Text to Columns... Choose the option
Delimited and then click on next choose the option space. Click on
next and then finish and everything should be separated into 2
columns.

THanks
Denis

 

[jetted]

Hi

I forgot if the name in the cell

follow this format (no space between firstname and lastname)
JohnSmith
MarySmith
BabySmith

Assuming the name are in column A and column B is empty you could you
use the following code:

Sub separate_firstname_lastname()
Dim trouve_ucase As String
flag1 = "NO"
rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
For i = 1 To rowcount
Range("a" & i).Select
entirename = ActiveCell.Value
check_len = Len(entirename)
check_len = check_len
For j = 1 To check_len
trouve_ucase = Mid(entirename, check_len - j, 1)
If trouve_ucase = "A" Or trouve_ucase = "B" Or trouve_ucase
= "C" Or trouve_ucase = "D" _
Or trouve_ucase = "E" Or trouve_ucase = "F" Or trouve_ucase
= "G" Or trouve_ucase = "H" _
Or trouve_ucase = "I" Or trouve_ucase = "J" Or trouve_ucase
= "K" Or trouve_ucase = "L" _
Or trouve_ucase = "M" Or trouve_ucase = "N" Or trouve_ucase
= "O" Or trouve_ucase = "P" _
Or trouve_ucase = "Q" Or trouve_ucase = "R" Or trouve_ucase
= "S" Or trouve_ucase = "T" _
Or trouve_ucase = "U" Or trouve_ucase = "V" Or trouve_ucase
= "W" Or trouve_ucase = "X" _
Or trouve_ucase = "Y" Or trouve_ucase = "Z" And flag1 =
"NO" Then

number1 = (check_len - j)
flag1 = "YES"
firstname = Left(entirename, number1 - 1)
lastname = Right(entirename, number1 - 1)
ActiveCell = firstname
ActiveCell.Offset(0, 1).Select
ActiveCell = lastname
GoTo line1:
End If
Next j
line1:
Next i
End Sub

 

[Ron Rosenfeld]

How can I separate a column of cells containing a single text string in
each[these are names formated as a string with no delimiter as in
"FirstLast" with the first letter of first and last name capitalized
and the rest lower case...] into two columns of text called Firstname
and Lastname?

Thanks!

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then with

A1: FirstnameLastname

Firstname: =REGEX.MID(A1,"[A-Z][^A-Z]+")
Lastname: =REGEX.MID(A1,"[A-Z][^A-Z]+",2)



--ron