Hi everyone. I was recently tasked with an Excel project that I am totally lost on. My director gave me instructions on entering a function, but I keep getting errors on it. Here is the basic story:

I made a spread sheet of contact information formatted like this in each cell:

Company
CEO
Address
City
State
Zip
Phone
Website

I entered 200 of these, but now they say they want each area in it's own column. So instead of being in one cell, now it must go across the sheet. To save time my director gave me these instructions:

Rather than retype the entire list, add the function below to a module in the workbook; it will add a comma at each carriage return. You will then be able to save it as a text file and import it into excel and break the data based on commas. You need to add a comma between the state and zip code.


Function add_comma(cell_value As String) As String
 
add_comma = Replace(cell_value, Chr(10), ",")
 
End Function


To do this easily, you need the list in one column. For this example say they are in column A starting in Cell A1. In cell B1, type the formula below.

=add_comma(A1)

Copy this formula down the entire list and it will remove the carriage return and add commas.

Once you have everything converted, copy the cells and paste as “values” to get rid of the formula and keep the data.

Save the spreadsheet with just the comma delimited data in a txt file.

Import into excel with “comma” as the separator and the text qualifier as “none”. You’ll have the data in columns and can add the headers.

I have very limited exposure to Excel, and I have tried everything I can think of to make this function work but I have not been able to so far. I would REALLY appreciate some advice on this.

Thank you!