Insert apostrophe in each cell

[Shari]

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?

 

[papou]

Hello Shari
Format your cells as text.

HTH
Cordially
Pascal

 

[Shari]

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.

 

[art]

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.

 

[papou]

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

HTH
Cordially
Pascal

 

[Ron Rosenfeld]

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

 

[Shari]

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

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

 

[Roger Govier]

Hi

Just select the column of Numbers>Data>Text to Columns>Next>Next>select
column type as Text>Finish
All of you numbers will be converted to text values ready for importing

 

[Shari]

I have already done that as I stated before.

That will not work.

 

[art]

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

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

 

[art]

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]

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

 

[Dave Peterson]

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

 

[Ron Rosenfeld]

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

 

[Roger Govier]

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>