Solved

Excel: Split names into columns Macro?

Posted on 2012-03-12
10
256 Views
Last Modified: 2012-04-01
We have a document filled with tons of data. There is one column that states people's names. Some with middle names, some with not. But the problem is it's all in one column.

What we need is a macro that can separate all of these into 3 separate columns.

I have a example of what I'm trying to say attached to this.

Thanks!
example.xlsx
0
Comment
Question by:Pancake_Effect
  • 4
  • 3
  • 2
10 Comments
 
LVL 47

Assisted Solution

by:Martin Liss
Martin Liss earned 250 total points
ID: 37712652
This is one way it could be done in VB.


Dim sFirst As String
Dim sMI As String
Dim sLast As String
Dim intPos As Integer
Dim strParts() As String

'remove comma space pair with comma
strName = Replace(strName, ", ", ",")
intPos = InStr(strName, ",")
sLast = Left(strName, intPos - 1)

If InStr(Mid(strName, intPos + 1), " ") Then
    strParts = Split(Mid(strName, intPos + 1), " ")
    sFirst = strParts(0)
    sMI = Left(strParts(1), 1)
Else
    sFirst = Mid(strName, intPos + 1)
    sMI = ""
End If
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 37712656
(Sorry I'm only familiar with macros) with that VB is there a way to specify columns such as A:A go to A:C? Because when we get this data it's filled with like 50 columns of other data that's not names, but other data.
0
 
LVL 47

Assisted Solution

by:Martin Liss
Martin Liss earned 250 total points
ID: 37712679
And I'm obviously not an Excel programmer but this works for one row. It assumes the data is in A1 and that B1, C1 and D1 are the output cells.


Sub Macro1()
    Dim strParts() As String


    strParts = Split(Range("A1").Value)
    Range("B1").Select
    ActiveCell.FormulaR1C1 = strParts(0)
   
    If UBound(strParts) = 2 Then
    ' there's a middle name
        Range("C1").Select
        ActiveCell.FormulaR1C1 = Left(strParts(1), 1)
        Range("D1").Select
        ActiveCell.FormulaR1C1 = strParts(2)
    Else
        Range("D1").Select
        ActiveCell.FormulaR1C1 = strParts(1)
    End If
End Sub
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 250 total points
ID: 37712715
The assumption for the following "name" formulas is there may/may not be a middle name.  Each name that is found, including punctuation is separated by the other names by one space and there are no other spaces, otherwise.

This formula finds the n-th occurrance of a character in a string.  We can separate the names by looking for the first space to get the first name, then second space (if it exists) to separate the middle from the last name.

For example, a non-case sensitive (using Search instead of Find) search for the first space in "John C. Wayne", you'd use something like:

=SEARCH("^^",SUBSTITUTE("John C. Wayne"," ","^^",1))

So the space is replaced with the characters "^^", then a search is performed on "^^" to find the position of the first instance of the space.

The second instance of space, if it exists would be using the formula:

=SEARCH("^^",SUBSTITUTE("John C. Wayne"," ","^^",2))

So, for your example, in cell C23, D23 and E23, we'd use variations of this formula to get the first, middle (if it exists), and last name, respectively.  And, we use the IFERROR to advise us whether there is or isn't a middle name (re: second space):

[C23]=LEFT(A23,SEARCH("^^",SUBSTITUTE(A23," ","^^",1))-1)
[D23]=IFERROR(MID(A23,SEARCH("^^",SUBSTITUTE(A23," ","^^",1))+1,SEARCH("^^",SUBSTITUTE(A23," ","^^",2))-SEARCH("^^",SUBSTITUTE(A23," ","^^",1))-1),"")
[E23]=IFERROR(RIGHT(A23,LEN(A23)-SEARCH("^^",SUBSTITUTE(A23," ","^^",2))),RIGHT(A23,LEN(A23)-LEN(C23)-1))

And you can copy down these formulas, as much as needed.

See attached.

Good Luck!

Dave
Example.xlsx
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37712722
There OUGHT to be a SPLIT function at the formula level, lol, but this approach is the closest I've found.

Dave
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 37712749
dlmille that looks very good!

One question, what values do I change if I want to change column or rows?

I tried changing some values and it just messes it up
0
 
LVL 42

Accepted Solution

by:
dlmille earned 250 total points
ID: 37712759
Well, the formulas all look at column A for starters, And formula in E column cheats and uses the length found in column C.  The formulas on row 23, work with A23.

You can drag A23 to another location, and it would change the references on C,D, E23.

But, assuming your data starts at A2, and you wanted results on B,C, and D2

[C2]=LEFT(A2,SEARCH("^^",SUBSTITUTE(A2," ","^^",1))-1)
[D2]=IFERROR(MID(A2,SEARCH("^^",SUBSTITUTE(A2," ","^^",1))+1,SEARCH("^^",SUBSTITUTE(A2," ","^^",2))-SEARCH("^^",SUBSTITUTE(A2," ","^^",1))-1),"")
[E2]=IFERROR(RIGHT(A2,LEN(A2)-SEARCH("^^",SUBSTITUTE(A2," ","^^",2))),RIGHT(A2,LEN(A2)-LEN(C2)-1))

Does that help?

If not, tell me what cell range have the name and where you want to put the first, mid, last and I'll provide that transformation.

Dave
0
 
LVL 4

Author Closing Comment

by:Pancake_Effect
ID: 37744441
Thanks!
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37763965
I'm sorry guys, but I've thought about this for a couple days, and feel that too much effort was spent by each of us with excellent solutions to warrant a B grade, and therefore I'm objecting.

@Pancake_Effort - please read the following guidelines for grading:

Grading Guidelines

A: The Expert(s) either provided you with a thorough and informative answer or with a link to information that answered your question.

B: The Expert(s) provided an acceptable solution, or a link to an acceptable solution but you needed more information to complete the task.

C: You have given the Expert(s) ample time to respond to your clarification posts and you have responded to each of their posts providing requested information, and the answer, after clarification, lacks finality or does not address the issue presented


Based on my re-read, it does not appear from my perspective that you needed more information to complete the task - in fact, it appears your understood the proposed solutions perfectly and were very happy with the result.

Dave
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question