Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How can I split data from 1 cell into 2 cells within excel

Posted on 2004-09-03
5
Medium Priority
?
1,091 Views
Last Modified: 2009-10-07
I have a leads list in MS Excel 2000, and the name field contains both LastName, FirstName. I would like to split the data in column A to (2) columns: FirstName and LastName. So Column A would be FirstName and ColumnB would be LastName.

Thanks much

--Drew
0
Comment
Question by:Drew_Mora
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 10

Expert Comment

by:mikeopolo
ID: 11977844
Let's say cell A1 contains
James, Mike

Then cell B1 can read:
=TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))
and will give "Mike"
(the trim function removes the space after the comma)

Cell C1 can contain:
=LEFT(A1,FIND(",",A1)-1)
and will give "James"

Hope this helps you

Regards
James, Mike (:-)
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 11978008
Hi Drew_Mora,
The Data...Text to Columns menu item has the functionality you need.
1) Insert a blank column to the right of the cells containing your name field
2) Select the cells containing your name field
3) Open the Data...Text to Columns menu item
4) Choose Delimited text and click Next
5) Check the boxes for "Space", "Comma" and "Treat consecutive delimiters as one"
6) Click Finish
Cheers!

Brad
0
 
LVL 11

Expert Comment

by:huntersvcs
ID: 11979566
Hi Drew_Mora,

"byundt" has the correct answer.  You'll only run into problems if cells include Titles (Dr. Prof. etc.), middle initials, or maybe double names.  You will have to manually adjust for these entries.  If there are too many, you could run a VB script to count the empty spaces and use this as a variable to control how many columns you want to offset the split entries.

Or even easier, use a script to check if the cell to the right is empty and if not, just insert an empty cell.

Say, for example, your "Names" are in column A:

    Range("B1").Select
    x = ActiveCell.Address
    ActiveCell.Offset(0, 1).Select
    If ActiveCell.Value = "" Then
        ActiveCell.Offset(0, -1).Select
        Selection.Insert Shift:=xlToRight
    End If
    Range(x).Select

You'll have to set in either a loop or a ForEach command, but I think you get the idea.
0
 
LVL 81

Expert Comment

by:byundt
ID: 12001370
Drew,
Thanks for the grade!
Brad
0
 

Expert Comment

by:cbehnke
ID: 12401782
Something I found quite by accident ...

While I was copy / pasting number sets from emails into an Excel sheet, I was doing the manual (long and slow) method for items like:

XXXX-123456-YYYY

I would copy the first set, then paste, copy the second, paste, etc etc... I figured there had to be an easier way, so I found this tip (byundt's works wonders).

However, by accident I found that when I copied the entire string and pasted it into the row below the last entry (which I had manually broken the data across 3 columns), that it would automatically break the portions into the columns!

XXXX |_| 123456 |_| YYYY

I'm guessing there's some kind of smart filter in Excel which adapts what you've been doing to what's coming next!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

This article was originally published on Monitis Blog, you can check it here . If you have responsibility for software in production, I bet you’d like to know more about it. I don’t mean that you’d like an extra peek into the bowels of the sourc…
One-stop solution for Exchange Administrators to address all MS Exchange Server issues, which is known by the name of Stellar Exchange Toolkit.
This video demonstrates basic masking and how to edit the mask to reveal the desired image.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

722 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