Apostrophe in Numbers

  • Thread starter Thread starter rivkee
  • Start date Start date
R

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!
 
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.
 
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
 
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
 
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!
 
Glad I could help

rivkee said:
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!
Click to expand...
 
Back
Top