Import txt file into Excel to create contacts list

[Marco Salvatori]

Hello,

I was given a list of contacts by a user as a text file.  It is formatted loosely as comma delimited values (CSV) but not very consistently.  It has some semicolons inserted after phone numbers, for some reason, and commas after some last names (not all), as well as a few email addresses associated with some names (again, not all).

My problem is when I import it to Excel 2016 the data just comes in as one long column, as opposed to a column with the names, another with the addresses, another with phone numbers, etc.. 

Is there a way to automate this to quickly create separate columns with the appropriate headings and pertinent data in them?  I don't want to spend too much time cleaning up the text file line by line of all the little issues it has.

Any help is appreciated.

[#2]

Hi Marco

Maybe try this?

1. In your Excel spreadsheet, go to Data tab > From Text.
2. Locate and open the CSV file you want to import.
3. By default it will have Delimited selected. 
4. If your CSV has headers, tick the My data has headers box.
5. Click the Next button.
6. Untick Tab anbd tick Comma in the Delimiters section. When you make this change, you will see the CSV data change in the Data preview section below (hopefully the data is looking to be grouped right).
7. Click the Next button.
8. You can choose how you want the columns to be formatted.
9. Click the Finish button when complete.

If there are still issues with the data, then it could be that the CSV wasn't consistently formatted which is throwing things off.

See how you go and let us know?

Cheers

Damien

[#3]

you can save text file as csv

open in notepad

go to save as

change file extension to csv

choose all files 

import from this csv file