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?
 
Subodh Tiwari (Neeraj)Connect With a Mentor 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
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Steve EckermanSystems AdministratorAuthor Commented:
i am not very versed with Excel.
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.