I understand that when there is an apostrophe in front of a number that is
only visible in the forumla bar, that the the number is text. I am trying to
figure out a way to add the apostrophe to a column of numbers. I need this
to be done in order for vlookups to give me an accurate value. Because of
links to querys from my database, I cannot convert the numberic texts to
numbers. I have to somehow convert the numbers to the text with an
apostrophe. I've searched and searched for an answer. Can anyone please
help? Thanks!

have an apostophe in, say, cell A1.

Assume your numbers are in column B

in column C, =A$1&B2

Paste down your column.

Copy your new column and paste special/values

They are now text.

"rivkee" wrote:

I understand that when there is an apostrophe in front of a number that is
only visible in the forumla bar, that the the number is text. I am trying to
figure out a way to add the apostrophe to a column of numbers. I need this
to be done in order for vlookups to give me an accurate value. Because of
links to querys from my database, I cannot convert the numberic texts to
numbers. I have to somehow convert the numbers to the text with an
apostrophe. I've searched and searched for an answer. Can anyone please
help? Thanks!

Hi,

This is a first, normally posters want to get rid of apostrophes. Right
click your sheet tab, view code and paste this in and run it. It assumes
column A so change to suit

Sub I_Hate_Apostrophes()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set Myrange = Range("A1:A" & lastrow)
For Each c In Myrange
If Not c.HasFormula And IsNumeric(c) Then
c.Value = "'" & c.Value
End If
Next
End Sub

Mike

"rivkee" wrote:

I understand that when there is an apostrophe in front of a number that is
only visible in the forumla bar, that the the number is text. I am trying to
figure out a way to add the apostrophe to a column of numbers. I need this
to be done in order for vlookups to give me an accurate value. Because of
links to querys from my database, I cannot convert the numberic texts to
numbers. I have to somehow convert the numbers to the text with an
apostrophe. I've searched and searched for an answer. Can anyone please
help? Thanks!

Private Sub addApostrophe()
Const whatColumn = "A" 'Change to your needs
Dim looper As Long 'looper = 5 Starting row of data
Dim lastToCheckRow As Long

lastToCheckRow = Range(whatColumn & Rows.Count).End(xlUp).Row

For looper = 5 To lastToCheckRow
Cells(looper, whatColumn).Value = "'" & Cells(looper,
whatColumn).Value
Next looper
End Sub

"rivkee" wrote:

I understand that when there is an apostrophe in front of a number that is
only visible in the forumla bar, that the the number is text. I am trying to
figure out a way to add the apostrophe to a column of numbers. I need this
to be done in order for vlookups to give me an accurate value. Because of
links to querys from my database, I cannot convert the numberic texts to
numbers. I have to somehow convert the numbers to the text with an
apostrophe. I've searched and searched for an answer. Can anyone please
help? Thanks!

I know... I haven't been able to find anything about adding apostrophes!
Thanks so much Mike H! Your code works perfectly. This is such a saver for
me. Cheers!


"Mike H" wrote:

Hi,

This is a first, normally posters want to get rid of apostrophes. Right
click your sheet tab, view code and paste this in and run it. It assumes
column A so change to suit

Sub I_Hate_Apostrophes()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set Myrange = Range("A1:A" & lastrow)
For Each c In Myrange
If Not c.HasFormula And IsNumeric(c) Then
c.Value = "'" & c.Value
End If
Next
End Sub

Mike

"rivkee" wrote:

I understand that when there is an apostrophe in front of a number that is
only visible in the forumla bar, that the the number is text. I am trying to
figure out a way to add the apostrophe to a column of numbers. I need this
to be done in order for vlookups to give me an accurate value. Because of
links to querys from my database, I cannot convert the numberic texts to
numbers. I have to somehow convert the numbers to the text with an
apostrophe. I've searched and searched for an answer. Can anyone please
help? Thanks!

Glad I could help

"rivkee" wrote:

I know... I haven't been able to find anything about adding apostrophes!
Thanks so much Mike H! Your code works perfectly. This is such a saver for
me. Cheers!


"Mike H" wrote:

Hi,

This is a first, normally posters want to get rid of apostrophes. Right
click your sheet tab, view code and paste this in and run it. It assumes
column A so change to suit

Sub I_Hate_Apostrophes()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set Myrange = Range("A1:A" & lastrow)
For Each c In Myrange
If Not c.HasFormula And IsNumeric(c) Then
c.Value = "'" & c.Value
End If
Next
End Sub

Mike

"rivkee" wrote:

I understand that when there is an apostrophe in front of a number that is
only visible in the forumla bar, that the the number is text. I am trying to
figure out a way to add the apostrophe to a column of numbers. I need this
to be done in order for vlookups to give me an accurate value. Because of
links to querys from my database, I cannot convert the numberic texts to
numbers. I have to somehow convert the numbers to the text with an
apostrophe. I've searched and searched for an answer. Can anyone please
help? Thanks!