Simple Problem

mikadillo

New Member
Joined
Apr 28, 2002
Messages
3
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top