Splitting Data In An Excel Spreadsheet

Assuming your contact is in A2, then try this...

FirstName:

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

Select allOpen in new window

LastName:

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

Select allOpen in new window

How do I make it run?

i am not very versed with Excel.

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?

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?

I am not getting you.
Can you upload a small sample workbook?

Here it is.
remitto04162018.xls

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?

Column C for First Name and Column D for Last Name.

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.

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

The ones that have / I would handle manually.

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.

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.

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.

Overwriting the column C would not be a wise move as you will loose the original contacts.

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.

That worked Subodh!  Thanks so much for your help and paitence!

You're welcome Steve! Glad I could help.