Plaintext and Excel

April 25, 2007 10:23 AM

MS Excel: I need to place quotation marks around the numbers in a column. Is there a better way to do this than I am currently trying or is there a plaintext spreadsheet editor that would be a better solution?

Right now, I end up inserting two columns filled with quotation marks around the column of numbers - saving as a csv - opening in Notepad (where the single marks appear as four marks) - edit and replace the spaces between the columns and replace the 4 quotation marks with one on either side of the number. This is then copied and pasted back into Excel where most of the time the quotation marks do not appear (even when the cells are formatted as text). Is there such a thing as a plaintext spreadsheet editor that would work better for this?

Example:
I need to turn this -
1001
1002
1003
Into this -
"1001"
"1002"
"1003"

posted by imposster to computers & internet (10 comments total)

I've been doing this for a similar task; it assumes that column A is the numbers you want in quotes.

B1 = char(34) & A1 & char(34)
B2 = char(34) & A1 & char(34)
etc.

Once you have column B worked out, you can either keep the quoted data as a separate column, or highlight column B, and do "paste special" on column A; select "Value" as the type of paste.
posted by inigo2 at 10:39 AM on April 25, 2007

Note that if you want to use those values now in quotes in any formulas, you'll have to refer to them differently (instead of just referring to cell "A1", for example). This should work:

MID(A1,2,LEN(A1)-2)
posted by inigo2 at 10:42 AM on April 25, 2007

Fantastic. Thanks.
posted by imposster at 10:43 AM on April 25, 2007

Oh, actually - how do I then make that column stand on its own (without the reference to the other column)? For example, for inserting it into another spreadsheet.
posted by imposster at 10:45 AM on April 25, 2007

Oh, sorry - you already answered that. Thanks again.
posted by imposster at 10:46 AM on April 25, 2007

Assuming the number is in cell A1, I just got this to work:

=""""&A1&""""
posted by PFL at 10:47 AM on April 25, 2007

Hmm, when I save as txt and view it with notepad, I still get multiple quotation marks. This is pretty easy to fix with find and replace, though.
posted by imposster at 10:54 AM on April 25, 2007

You could change the format to custom and then in custom, place ' (Double single quotes) then # or 0 for the number, then another ' (double single quotes).
posted by defcom1 at 11:12 AM on April 25, 2007

Don't save as text. Just copy and paste into notepad. Excel inserts wacky quotation marks when you save as text.
posted by some chick at 12:20 PM on April 25, 2007

I would probably use the concatenate command to preserve the original column. But as with anything in Excel there are many ways to do this.
posted by loiseau at 3:47 PM on April 25, 2007