Splitting Data In An Excel Spreadsheet

Hello Experts, I have an Excel sheet that has a firstname column and a last name column.  However the worksheet I am getting my information from only has a column called contact which contains both the first and last name seperated by a space.  So I cut and pasted the contact column information into the first_name column in the other worksheet.  Is there a way to move the last name from  the first name column into the last name column automatically?  Please advise.  Thanks
Steve EckermanSystems AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Assuming your contact is in A2, then try this...

FirstName:
=LEFT(A2,FIND(" ",A2)-1)

Open in new window

LastName:
=RIGHT(A2,LEN(A2)-FIND(" ",A2))

Open in new window

0
Steve EckermanSystems AdministratorAuthor Commented:
How do I make it run?
0
Steve EckermanSystems AdministratorAuthor Commented:
i am not very versed with Excel.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You are supposed to place the formula suggested in blank columns.
e.g. in your contact names are in column A, then

In B2, place the formula for FirstName and then copy it down.
In C2, place the formula for LastName and then copy it down.

Are you trying to achieve it with the help of VBA? If yes, let me know how would like to run it.
Also which column would contain the Contacts and in which columns you would like to place First and Last Names?
0
Steve EckermanSystems AdministratorAuthor Commented:
It worked but when I copy the data into the first and last name columns all I get if a REF.  Is there a way to make that stop?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I am not getting you.
Can you upload a small sample workbook?
0
Steve EckermanSystems AdministratorAuthor Commented:
Here it is.
remitto04162018.xls
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
It is Column J which you want to split. Right?
In which columns you want to place the First and Last Names?

Some of the contacts also contain a "/" character like MATT HUFF/SHANNON in J3. What would be your expected output in this case?
0
Steve EckermanSystems AdministratorAuthor Commented:
Column C for First Name and Column D for Last Name.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Column C and D already have data in them. Do you want to overwrite them with the formulas?

Also, you didn't answer all the queries from my last post.
0
Steve EckermanSystems AdministratorAuthor Commented:
I apologize Subodh I uploaded the wrong file.  Here is the correct file. I would like to take the data from columns L and M and overwrite the data in columns C and D respectively.  But not the formulas because that causes the REF error.  Thanks,
vendorcontacts04162018.xls
0
Steve EckermanSystems AdministratorAuthor Commented:
The ones that have / I would handle manually.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Will you please take a minute to explain what you are trying to achieve here?
Here are some queries?

1) Which column is the original column with Contacts (First+Last Name)? It's Column C. Right?
2) What's the point in first populating the column L and M with the formulas and then overwriting the column C and D?
3) There are some Last Names already populated in column D, do you want to retain them in column D while overwriting the column D? Did you populate them manually? Please remember that a cell can either have a formula or a manual input, not both.
4) Is column D blank originally or you insert a new column?

Overall can you explain the whole process of splitting the names?

I guess, you need a VBA solution to get the desired output. If so, let me know if you have Contacts in Column C always.
0
Steve EckermanSystems AdministratorAuthor Commented:
Good Morning Subodh,

The contacts data comes from another Excel spreadsheet and yes it is column C.

Column L and M were the blank columns need to extract and seperate the first and last names then I was hoping to copy and paste into C and D to get the result I desired.

Yes at first I attempted to manually cut and paste the last name into the last name column but it would take to much time.

Column D was blank so you can overwrite anything that is already in there.

I copied the contacts from the other spreadsheet into column C and now I need to seperate the first name from the last name.  First name in column C last name in column D.

Thanks so much for your help.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
What I would suggest is, let the original column C be remain intact and split the first name and last name in two difference columns say col. L and M. That way you have the original contacts in column C in place which you can use for comparing the formula outputs in column L and M so if a name is not split correctly, you have an option to correct it manually.

OR instead of using column L and M for the formulas, I would suggest that you insert a new column between column C and D (where D is already blank, as you said). So in the end you have two blank columns D and E where you can use the formulas to split first and last names.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Overwriting the column C would not be a wise move as you will loose the original contacts.
0
Steve EckermanSystems AdministratorAuthor Commented:
Hello Subodh,  The template is from a vendor for a data load into our new ERP\MRP system so it cannot be altered.  When all is done it must be in the same format that I recieved it.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay I understand that.
So if you want to overwrite the columns C:D with columns L:M after making sure that all the contacts from column C are correctly split in columns L and M with the help of a formula and manual tweaks, follow these steps...

1) Select the range L2:M637.
2) Press Ctrl+C to copy.
3) Right click in cell C2, choose Paste Special and choose Values and click OK.

OR after copying the range L2:M637, select C2 and press Alt,E,S,V (a shortcut key to paste as values)

Then you can delete the column L:M.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve EckermanSystems AdministratorAuthor Commented:
That worked Subodh!  Thanks so much for your help and paitence!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Steve! Glad I could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.