Simple Problem

[mikadillo]

I am a newbie to Excel (forced away from Lotus) and have a large work sheet that I want to put double quotes around the data in only 1 column. I have tried but can't seem to find the answer. There must be a simple solution. Thanks for the help.

[Robb__]

mikadillo

Try typing ' before the entry ie '"123"

Any help?

Regards

Robb__

[mikadillo]

Thanks for the quick answer Robb but that only puts it in the top cell. I have thousands of rows and to get it in all of them. I am a rank amateur at this and so maybe I did it wrong.

[Iain Lewis]

Have a look at the add in available from

http://www.asap-utilities.com/

One of it's many functions is the ability to add a character(s) at the start of a cell or a range of cells.

[Aladin Akyurek]

On 2002-04-29 05:46, mikadillo wrote:
I am a newbie to Excel (forced away from Lotus) and have a large work sheet that I want to put double quotes around the data in only 1 column. I have tried but can't seem to find the answer. There must be a simple solution. Thanks for the help.

In B1 enter:

=CHAR(34)&A1&CHAR(34)

where A1 holds the first entry to wrap between double quotes.

Give a double click on the fill handle (the little black square) in the lower-right corner of B1 in order to copy down the formula.

May I ask why you need to do this double quoting?

[Tom Urtis]

You may also want to try a VBA work-around, if you want to achieve this effect automatically as you enter values in that column.

Right click on your sheet tab, left click on View Code, and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Or Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Target = "'''" & Target & "''"
Application.EnableEvents = True
End Sub

Please note a few important points:

(1) This code is for column A (that's what the "1" means in the line "Target.Column <> 1 "). Modify for column number as needed.

(2) Any numbers you enter will not be of much use to you in trying to do calculations with them, unless you use some advance formula techniques.

(3) It will work with drag and drop but not with AutoFill when the source active cell is outside column A.

(4) You will lose the Undo feature on this worksheet.

FYI, the quotes within the quotes are actually apostrophes. The preceding quotes are 3 apostrophes, and the proceeding ones are 2 apostrophes.

Kind of ugly looking code but I think it does what you are asking, in addition to the formula solutions offered above.

Any help?

_________________
Tom Urtis

[ This Message was edited by: Tom Urtis on 2002-04-29 06:39 ]

[mikadillo]

Thanks for the replies I will try to get to them later today. I am exporting data from a practice management program in various parts to create a final CSV file for import to a new program. The new program requires that the the data in one field be in double quotes in order to import it. Its 4,000 rows and 40 columns.

[croweld89]

Maybe an easier solution for a "Excel Newbie" would be to find/replace all quotation marks with '"

I am assuming that you already have quotations around all numbers. Simply, hold down the alt key and press the H key. Put " in the find field and '" in the replace field.

I would hit the find next key first to make sure you get the results you want, and then the replace all key.

-Dave