how to put quotation mark in each cell of the column

[crazyfisher]

Dear all,

just want to know is it possible to autometically put a double quotation
mark in the cell content within the same column? because there are 84 cells,
i don't want to do it manually...

thanks very much !

 

[Mike]

VBA Can do it.
This code will work on Column A Cells A1 thru the last Used cell in Column A
I'm guessing you want to put "" around Values you already have in the cells.

Before running code
Cell A1 value = Test
after you run the code
Cell A1 = "Test"

Sub addDoubleQuotation()
lastcell = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastcell
Range("A" & i).Value = """" & Range("A" & i).Value & """"
Next
End Sub

 

[Gord Dibben]

In namebox type A1:A84 and hit Enter key.

Type " in active cell and hit CTRL + Enter together

Just curious, but why would you want to do this?


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Could be I missed the point of OP's question<g>


Gord

 

[FSt1]

hi gord,
I was assuming he had data in the cell and wanted to right justify it all.
but i have never figured out how to do that yet.

Regards
FSt1

 

[Gord Dibben]

You can't add double quotes to right-justify. If you have Transition options
enabled it will be automatic.


Gord

 

[FSt1]

hi
you're right. but put 777 in a cell then add a double quote to it. it right
justifies and becomes text. his whole post confused me. I re-read it several
times. and he seems to be asking for a single double quote.
the symbols ', ^ and " are use by xl (and lotus) to signify wheather text
is to be left justified , centered or right justified. but if you put a
single double quote in a cell with no text data, the double quote doesn't
show.(nor do the other symbols) if you do have text data, it right justifies.
provide you don't wipe out the data adding it. and if all you wanted to do is
right justify then why not just high light the date and click the right
justify icon. your way worked too.
you don't know how close i come to asking the same question you did. why?

Regards
FSt1

 

[FSt1]

hi
better example.
put 777 in a cell and but a single quote before it. it now left justifes.
numbers right justify by default.

FSt1

 

[crazyfisher]

Thanks Gord,

Sorry for my ignorance, when I type in A1:A84 and hit enter key, it becomes
A1 itself.. and when you say type " in active cell, which active cell do you
mean?

thanks vey much!
I am just trying to do that so I can convert the excel table into a text
file, and then I can use the text file as an input for a macro. and this
column is all the time value(such as 9:00AM, etc)
I know it is a strange process, but that is my school assignment..couldn'
think of a better way to do that ..

 

[crazyfisher]

Gord,
I just figure out what you mean now, I should type" both at the end and the
beginning, how stupid I am. it is working now...
so thanks all !

 

[crazyfisher]

the strange thing about this way is that all the value is reset to the active
cell one...
do I do sth wrong about that?

 

[Gord Dibben]

The namebox is the box at left end of the formula bar.

It shows which cell is selected.

Select A1 and then click in the namebox and type in A1:A84 and hit Enter key.

A1:A84 will be highlighted with A1 as the active cell.

But you don't want to enter quote marks if you already have the time values in
the cells.

Just save the file as a CSV(comma de-limited) (*.csv) or Text(Tab delimited)
(*.txt) file and use that as your input file.


Gord

 

[Gord Dibben]

Yes.

The original data will not be retained using that method.

See next post about saving as a *.txt file without adding any quote marks in
cells.


Gord

 

[crazyfisher]

Yes, that is not what i want to see....
I was using the code by Mike, and it is working fine for general cells, but
what if it is cells with formats? such as time value? such as 9 am,. if I use
the code, it will change 9am to "0.375", I understand I should change the
code to accomdate this , but I am not sure how.
:

 

[Gord Dibben]

Another method to change the time values to text.

In B1 enter =TEXT(A1,"h:mm AM/PM")

Copy and paste special>values>ok>esc

Delete column A


Gord

 

[Led_A_Muck]

Hi Mike

I not sure I understand your formula. New to excel and not sure how to use
the formula. Where are you supposed to type "Cell A1 value = Test" ? The
formula listed below is that supposed to be typed in the function bar? Can
you please provide a step by step I would greatly appreciate it.

Sub addDoubleQuotation()

lastcell = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastcell
Range("A" & i).Value = """" & Range("A" & i).Value & """"
Next
End Sub

 

[Gord Dibben]

Mike's suggestion is to use a VBA macro to make the changes in place.

Might be easier for you to enter =""""&A1&"""" in B1 then double-click on
the fill handle to copy down.

Copy column B then select column A and Edit>Paste Special>Values>OK>Esc


Gord Dibben MS Excel MVP

 

[Led_A_Muck]

Gord Dibben-

It worked! Thanks so much.

Mike's suggestion is to use a VBA macro to make the changes in place.

Might be easier for you to enter =""""&A1&"""" in B1 then double-click on
the fill handle to copy down.

Copy column B then select column A and Edit>Paste Special>Values>OK>Esc


Gord Dibben MS Excel MVP