Apostrophe in Numbers

[rivkee]

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!

 

[Sean Timmons]

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.

 

[Mike H]

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

 

[Mike]

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]

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]

Glad I could help

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!