asked on

Seperating Johnson/Paul in cell into cloumn name lastname: in cell Johnson column name Firstname cell Paul. Break up the string into last and first name

I have a cell with the following information Timm/Tomm. I created 2 columns one called Last Name and one called First Name. I need to create a formula or any ideas? to take the combined cell Timm/Tom and seperate them into
Last Name First Name
Timm Tom
I dont want to seperate them manually as the other person did --- there are 800 records -- All names are different in the spreadsheet. They all have the / between the strings. ie Johnson/John - same format

Daniel Pineault

Assuming A1 contains Timm/Tomm, then

Last Name (B1) =LEFT(A1, SEARCH("/",A1,1)-1)
First Name (C1) =RIGHT(A1,LEN(A1)-LEN(B1)-1)

Karen Falandays

FIrst insert two new columns to handle the new fields
Select the text
Choose Data>Text to columns
Select delimited, with the / as the delimiter
Select the top left cell where the new data will go

Patricia Timm

ASKER

I used Karen's method above and it worked out fabulous!
Only one thing
I have a string
800-324-4546,800-324-5467,800-324-4589
I would like the first to remain in its current column
example
ColA Currently
800-324-4546,800-324-5467,800-324-4589
After
ColA ColB ColC
800-324-4546 800-324-5467 800-324-4589
I can work around it and change column names to match but an extra step.
When I use the data and text to columns it moves the entire string to new columns. Thanks

ASKER CERTIFIED SOLUTION

Karen Falandays

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patricia Timm

ASKER

Thanks!