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?

  • 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 '17 at 18:51
  • ok i have edited the question to reflect the data provided to me – Zach Smith Aug 4 '17 at 18:54
  • Just split each line on the space when you import it. – Ron Rosenfeld Aug 4 '17 at 19:10
  • @RonRosenfeld how can i split a space while moving it to a new column? – Zach Smith Aug 4 '17 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 '17 at 19:33
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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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