For exporting, I am trying to insert an apostrophe in each cell in one of my
columns that contains numbers. There are over 800 records, how do I do this
without having to go to each cell and do it manually?

Hello Shari
Format your cells as text.

HTH
Cordially
Pascal

"Shari" a écrit dans le message de news:
...
For exporting, I am trying to insert an apostrophe in each cell in one of
my
columns that contains numbers. There are over 800 records, how do I do
this
without having to go to each cell and do it manually?

I have already done that. I need to know how to insert an apostrophe into
each cell because they are numbers, being exported into Access and there is a
glitch trying to import it.

Please tell me how to insert an apostrophe in each cell.

"papou" wrote:

Hello Shari
Format your cells as text.

HTH
Cordially
Pascal

"Shari" a écrit dans le message de news:
...
For exporting, I am trying to insert an apostrophe in each cell in one of
my
columns that contains numbers. There are over 800 records, how do I do
this
without having to go to each cell and do it manually?

If in column A you have the list of numbers and words, put in column B this
formula:

=IF(IFERROR(VALUE(A1),"")="","",CONCATENATE("'",A1 ))

Hope this helps.


"Shari" wrote:

For exporting, I am trying to insert an apostrophe in each cell in one of my
columns that contains numbers. There are over 800 records, how do I do this
without having to go to each cell and do it manually?

Please amend to your needs:
For Each c In Range("A1:A3")
c.Value = "'" & c.Value
Next c

HTH
Cordially
Pascal

"Shari" a écrit dans le message de news:
...
I have already done that. I need to know how to insert an apostrophe into
each cell because they are numbers, being exported into Access and there
is a
glitch trying to import it.

Please tell me how to insert an apostrophe in each cell.

"papou" wrote:

Hello Shari
Format your cells as text.

HTH
Cordially
Pascal

"Shari" a écrit dans le message de
news:
...
For exporting, I am trying to insert an apostrophe in each cell in one
of
my
columns that contains numbers. There are over 800 records, how do I do
this
without having to go to each cell and do it manually?

On Tue, 4 Mar 2008 07:08:02 -0800, Shari
wrote:

For exporting, I am trying to insert an apostrophe in each cell in one of my
columns that contains numbers. There are over 800 records, how do I do this
without having to go to each cell and do it manually?

Where do you want to insert the apostrophe? Beginning, end, middle, third
character from the right ????
--ron

I was told to put it in front of each entry.

Also, the other formulas I was given are not working.

"Ron Rosenfeld" wrote:

On Tue, 4 Mar 2008 07:08:02 -0800, Shari
wrote:

For exporting, I am trying to insert an apostrophe in each cell in one of my
columns that contains numbers. There are over 800 records, how do I do this
without having to go to each cell and do it manually?

Where do you want to insert the apostrophe? Beginning, end, middle, third
character from the right ????
--ron

Hi

Just select the column of NumbersDataText to ColumnsNextNextselect
column type as TextFinish
All of you numbers will be converted to text values ready for importing

--
Regards
Roger Govier

"Shari" wrote in message
...
I was told to put it in front of each entry.

Also, the other formulas I was given are not working.

"Ron Rosenfeld" wrote:

On Tue, 4 Mar 2008 07:08:02 -0800, Shari

wrote:

For exporting, I am trying to insert an apostrophe in each cell in one
of my
columns that contains numbers. There are over 800 records, how do I do
this
without having to go to each cell and do it manually?

Where do you want to insert the apostrophe? Beginning, end, middle,
third
character from the right ????
--ron

I have already done that as I stated before.

That will not work.

"Roger Govier" wrote:

Hi

Just select the column of NumbersDataText to ColumnsNextNextselect
column type as TextFinish
All of you numbers will be converted to text values ready for importing

--
Regards
Roger Govier

"Shari" wrote in message
...
I was told to put it in front of each entry.

Also, the other formulas I was given are not working.

"Ron Rosenfeld" wrote:

On Tue, 4 Mar 2008 07:08:02 -0800, Shari

wrote:

For exporting, I am trying to insert an apostrophe in each cell in one
of my
columns that contains numbers. There are over 800 records, how do I do
this
without having to go to each cell and do it manually?

Where do you want to insert the apostrophe? Beginning, end, middle,
third
character from the right ????
--ron

=IF(IFERROR(VALUE(A1),"")="","",CONCATENATE("'",A1 ))

You might not be able to use this formula if you don't have office 2007.

"Shari" wrote:

I was told to put it in front of each entry.

Also, the other formulas I was given are not working.

"Ron Rosenfeld" wrote:

On Tue, 4 Mar 2008 07:08:02 -0800, Shari
wrote:

For exporting, I am trying to insert an apostrophe in each cell in one of my
columns that contains numbers. There are over 800 records, how do I do this
without having to go to each cell and do it manually?

Where do you want to insert the apostrophe? Beginning, end, middle, third
character from the right ????
--ron

If you can sort them, then enter:

=CONCATENATE("'",A1)

This should work good, but it will add it to the words as well. Try it and
let me know if you come up with any problem.

"Shari" wrote:

I was told to put it in front of each entry.

Also, the other formulas I was given are not working.

"Ron Rosenfeld" wrote:

On Tue, 4 Mar 2008 07:08:02 -0800, Shari
wrote:

For exporting, I am trying to insert an apostrophe in each cell in one of my
columns that contains numbers. There are over 800 records, how do I do this
without having to go to each cell and do it manually?

Where do you want to insert the apostrophe? Beginning, end, middle, third
character from the right ????
--ron

Art I already tried that and it did not work. I am using Office 2003.

"art" wrote:

=IF(IFERROR(VALUE(A1),"")="","",CONCATENATE("'",A1 ))

You might not be able to use this formula if you don't have office 2007.

"Shari" wrote:

I was told to put it in front of each entry.

Also, the other formulas I was given are not working.

"Ron Rosenfeld" wrote:

On Tue, 4 Mar 2008 07:08:02 -0800, Shari
wrote:

For exporting, I am trying to insert an apostrophe in each cell in one of my
columns that contains numbers. There are over 800 records, how do I do this
without having to go to each cell and do it manually?

Where do you want to insert the apostrophe? Beginning, end, middle, third
character from the right ????
--ron

You could use a macro:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
myCell.Value = "'" & myCell.Value
Next myCell

End Sub

I inserted the apostrophe in cells in column A.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Shari wrote:

For exporting, I am trying to insert an apostrophe in each cell in one of my
columns that contains numbers. There are over 800 records, how do I do this
without having to go to each cell and do it manually?

--

Dave Peterson

On Tue, 4 Mar 2008 07:53:01 -0800, Shari
wrote:

I was told to put it in front of each entry.

Also, the other formulas I was given are not working.

Use this macro.

To enter the macro, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use the macro, select any cell in the column to be processed.

Then, <alt-F8 opens the Macro Dialog box. Select InsertApostrophe and <RUN.

===============================
Option Explicit
Sub InsertApostrophe()
Dim lLastRow As Long
Dim rCol As Range
Dim lCol As Long
Dim c As Range

lCol = Selection.Column
lLastRow = Cells(65536, lCol).End(xlUp).Row
Set rCol = Range(Cells(1, lCol), Cells(lLastRow, lCol))

For Each c In rCol
If IsNumeric(c.Value) And Len(c.Value) 0 Then
c.Value = "'" & c.Value
End If
Next c
End Sub
===========================
--ron

Hi

I cannot see anywhere in your previous postings that you have said you have
done that.
You said you had formatted the column as Text. That is not at all the same
as what I told you to do.
Try doing what I said - you might get a nice surprise<g
--
Regards
Roger Govier

"Shari" wrote in message
...
I have already done that as I stated before.

That will not work.

"Roger Govier" wrote:

Hi

Just select the column of NumbersDataText to ColumnsNextNextselect
column type as TextFinish
All of you numbers will be converted to text values ready for importing

--
Regards
Roger Govier

"Shari" wrote in message
...
I was told to put it in front of each entry.

Also, the other formulas I was given are not working.

"Ron Rosenfeld" wrote:

On Tue, 4 Mar 2008 07:08:02 -0800, Shari

wrote:

For exporting, I am trying to insert an apostrophe in each cell in
one
of my
columns that contains numbers. There are over 800 records, how do I
do
this
without having to go to each cell and do it manually?

Where do you want to insert the apostrophe? Beginning, end, middle,
third
character from the right ????
--ron