The Coin Return Foundational Fundraiser is here! Please donate!
Way to automate this task? Excel? Notepad? Anything?
Figgy
Fighter of the night manChampion of the sunRegistered User regular
Fighter of the night manChampion of the sunRegistered User regular
I need to compile subscriber database using a very messy, unformatted series of text and rtf files. Here is an example of some of the lines in the files:
Joe brown smith jbs@email.com
Bill's Towing btowing@email.com
Bradley brad@email.com
I basically want to create an excel sheet with 1 column for the e-mail address and 1 column for everything else. Is there a way to do this? I can fiddle with inserting commas and turning the file into a CSV, which would create columns, but that would involve me manually putting the comma in the right spot on each line. A 'find and replace' to change spaces to commas would add spaces between names, multi-word companies, etc.
Is there a formula I can use in excel? Maybe paste the whole shebang into a single column, use a formula to insert a comma in the first space from the right, then turn that into a CSV to create the column breaks?
Joe brown smith jbs@email.com
Bill's Towing btowing@email.com
Bradley brad@email.com
I basically want to create an excel sheet with 1 column for the e-mail address and 1 column for everything else. Is there a way to do this? I can fiddle with inserting commas and turning the file into a CSV, which would create columns, but that would involve me manually putting the comma in the right spot on each line. A 'find and replace' to change spaces to commas would add spaces between names, multi-word companies, etc.
Is there a formula I can use in excel? Maybe paste the whole shebang into a single column, use a formula to insert a comma in the first space from the right, then turn that into a CSV to create the column breaks?
0
This discussion has been closed.
Posts
I everything into excel, so each line was in its own row, single cell.
First, return everything before the last space in each cell, which is everything except the e-mail address:
=LEFT(TRIM(A1),FIND("~",SUBSTITUTE(A1," ","~",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1)Paste that into column 1 of a new sheet.
Then, return the last "word" in each cell, which was the e-mail address:
Paste that into column 2 of the new sheet.