Link to home
Start Free TrialLog in
Avatar of brothertruffle880
brothertruffle880Flag for United States of America

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!
Avatar of jsdray
jsdray
Flag of United States of America image

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?
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
Avatar of brothertruffle880

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.
ASKER CERTIFIED SOLUTION
Avatar of jppinto
jppinto
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@JPPINTO:

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

Open in new window

PERFECT!!!!!  Thanks!