adding quotations marks " " arund cell text

[bindi]

I am trying to export some data from excel into another program, the program requires the text in the cells to be within quotation marks
eg cell= 1 hutton street
i need to add quotes to the beginning and end so the new cell would read
cell="1 hutton street"
the text within the cells is of different lengths
thanks
bindi

 

[Zathras]

Assuming you have:

A1: '1 hutton street

then you can insert a new column "B" and put in this formula:

B1: ="cell="""&A1&""""

Then copy the formula from B1 to B2 thru however many rows have data in column A.
Select column B and type Ctrl-C to copy.
Select Edit/Paste Special... from the menu, select "Values" and click OK and press enter.

The easiest way to get a double quote symbol when stringing together cell data is to put two quotes together. And to treat that as a piece of string, you need to put quotes around that, for a total of four quotes in all. If you prefer, here is an alternate way to achieve the same results:

B1: ="cell="&CHAR(34)&A1&CHAR(34)

Use whichever way you are most comftortable with.

Of course if you don't really want the

 cell=

part, just leave it out and type:

B1: =""""&A1&""""

or

B1: =CHAR(34)&A1&CHAR(34)

 

[lambic]

If you want to export the data with quotes as text qualifiers & also have the data comma delimited, you need to use VBA code.
There is an article on the Microsoft Product Support Site detailing how to achieve this (you can even copy & paste the code sample rather than retyping it).
Go to Microsoft product Support, then serach the Knowledege Base for article 'Q213448' for details.

Hope this may be of use!

 

[Zathras]

lambic is quite correct. My response over-simplified the situation.

If you have any imbedded double-quote characters in your data, they would have to be replaced by two double-quotes to be handled correctly.

But, are you absolutely sure that all of the cells need to be quoted? When you save as a .csv file, Excel will put quotes around cells where necessary (i.e., where the cell contains a double-quote or a comma) and will double up imbedded double-quotes automatically.

Before spending a lot of time doing possibly unnecessary work, I would try saving as a .csv file and see whether the program that is going to read it can work with it the way it is. You might be pleasantly suprised.

 

[bindi]

thanks very much for your response i fear that the vba programming is a lil beyond me but i have uses the char(34) command, saved the doc as a csv file, opened it up in word and then used the replace function to get rid of the extra ""
thanks again for your help bindi