Adding double quotes to text in Excel

By Patricia Egen • January 21st, 2013

If you have ever worked with drop down lists in Sage ACT, you know that you can import items to populate the list.  The list has to be a text file with the items one per line, enclosed in double quotes.

I like to use Excel to key in the data, sort it, remove duplicates, etc.  Then save the file as a text file.  However, it doesn’t put in the double quotes.

Here’s a formula you can insert in a column next to the data.  Assume the text items are in the A Column.

=concatenate(char(34),a1,char(34))

Put the formula in the B column and copy down to the last line of text in the A Column.  Copy the B column and paste special values into the A column.  Delete the B column.

At this point, because Excel still does weird things when I try to save it as text, I copy the A Column and paste it into a notepad, and save it as txt.  Now I have a nice clean list to import into my dropdowns.

 

Leave a Comment

« | Home | »