brothertruffle880
asked on
Excel 2003/2007/2010 - One Cell - How to split first name, middle initial, last name and "Jr."
Text to columns is nice but is there any way to further break down cell contents?
I'd like to have the middle initial appended to the first name and the "Jr." appended to the last name.
Help!
I'd like to have the middle initial appended to the first name and the "Jr." appended to the last name.
Help!
Confused me... are you trying to split as the title states, or append as the above question states?
never mind... sorry, I read it again more carefully...
So you have a single column with first mi last suffix and your'd like 2 columns. One with first mi and the second with last suffix... is this correct?
do all entries have a middle initial?
Do you want the result in a different column or replace existing?
So you have a single column with first mi last suffix and your'd like 2 columns. One with first mi and the second with last suffix... is this correct?
do all entries have a middle initial?
Do you want the result in a different column or replace existing?
Please take a look at this article to get an idea how to use it. You didn't gave any data example so it's better for you to build your own formula.
jppinto
jppinto
ASKER
I have one cell with the following: John T. Jones Jr.
I use text to columns and get the following:
col A col B Col C Col D
John T. Jones Jr.
I would like to get:
Col A col B
John T. Jones Jr.
I use text to columns and get the following:
col A col B Col C Col D
John T. Jones Jr.
I would like to get:
Col A col B
John T. Jones Jr.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@JPPINTO:
I didn't see any link to the article referred to in your posting. Or is the new EE interface buggy..
I didn't see any link to the article referred to in your posting. Or is the new EE interface buggy..
Sub Macro1()
'
' Macro1 Macro
'
'
Dim x
Dim first, mi, last, suffix
x = 1
nextone:
first = Range("A" & x).Value
mi = Range("B" & x).Value
last = Range("C" & x).Value
suffix = Range("D" & x).Value
If first = "" Then Exit Sub
If mi <> "" Then
Range("A" & x).Value = first & " " & mi
End If
If suffix <> "" Then
Range("B" & x).Value = last & " " & suffix
Range("C" & x).Value = ""
Range("D" & x).Value = ""
Else
Range("B" & x).Value = last & " " & suffix
Range("C" & x).Value = ""
Range("D" & x).Value = ""
End If
x = x + 1
GoTo nextone
End Sub
ASKER
PERFECT!!!!! Thanks!