ok so i have a client who has over 15,000 records in a csv file, with the column format:

full name | email

example of the data they have received is such as:

rex harrison [email protected]
tater smith [email protected]
bob traits [email protected]

i need to import these 15,000 records into their newsletter service. while they do allow csv import, their column structure should be the following format:

fist name | last name | email

with the following as examples:

rex harrison [email protected]
tater smith [email protected]
bob traits [email protected]

so that means i have to go into the file client gave me, and copy and paste each record's last name into a new column called 'last name'.

i am wondering if there is a quick and easy way to do this via excel, google drive, or any third party service?

  • 1
    That depends on how the full name is presented, is it LastName, FirstName or FirstName LastName are there Last Names with spaces in them like de Souza. Some test data would go a long way to help us help you. Make sure the test data covers all possible inputs. Aug 4, 2017 at 18:51
  • ok i have edited the question to reflect the data provided to me
    – Zach Smith
    Aug 4, 2017 at 18:54
  • Just split each line on the space when you import it. Aug 4, 2017 at 19:10
  • @RonRosenfeld how can i split a space while moving it to a new column?
    – Zach Smith
    Aug 4, 2017 at 19:13
  • There's plenty of information as to how to do that. Start by using Help for Excel "split on space". Aug 4, 2017 at 19:33

2 Answers 2


As I wrote in my comments, if your data is as you show it, you just need to split each row on the space when you import it. To do that, you use the Text-To-Columns wizard. Looking at Excel HELP for split on space will bring up the necessary information.


I give below my answer based on String Formulas :

  1. Copy the CSV File and Paste it In Column A

  2. Titles of Columns : Column A : " CSV Import " Column B : " First Name " Column C : " Second Name " Column D : " Email " Column E : " Length of CSV" Column F : " First Space At" Column G : " Second Space At "

  3. Formulas to be entered : B2 :---> =LEFT(A2,F2) C2 :---> =MID(A2,(F2+1),(G2-F2)) D2 :---> =RIGHT(A2,(E2-G2)) E2 :---> =LEN(A2) F2 :---> =FIND(" ",A2,1) G2 :---> =FIND(" ",A2,(F2+1))

  4. Copy All Formulas : B2..To .. G2 Down the Rows.

  5. Copy this Worksheet in another One for future reference of the Formulas.

  6. After all cells are evaluated, in the first worksheet, please Copy entire data and again paste the values so as to avoid any changes by mistakes.

  7. I hope it must work. For any queries, please let me know.


Harshad Vegda


You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .