0

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 test@email.com
tater smith rere@test.com
bob traits tree@house.com

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 test@email.com
tater smith rere@test.com
bob traits tree@house.com

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?

Improve this question
6
  • 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.
    – Scott Craner
    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.
    – Ron Rosenfeld
    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".
    – Ron Rosenfeld
    Aug 4, 2017 at 19:33

2 Answers 2

Reset to default
0

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.

Improve this answer
0
0

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.

Thanks.

Harshad Vegda

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.