From Numbers to Text

[Dianna]

I have a large amount of Order Numbers in one sheet and a
table with Order Numbers and additional data in another
sheet. I want to use vlookup to pull from one table to
the other based on the Order Number. The problem is that
in one table the Order Number exported from Access as
Text, in the oother sheet it is treated as a number. The
only way I have found to solve the problem is to change
the cell format to text, then hit the F2 key to edit and
enter to close the edit. If I just change the cell
format, the formula still does not recognize them as the
same data.

 

[J.E. McGimpsey]

One way:

you can convert the "text numbers" to real numbers by copying a
blank cell, selecting your data and choosing paste Special,
selecting the Value and Add radio buttons. This coerces the text to
real numbers.

Or you could change your VLOOKUP values to look for text:

=VLOOKUP(TEXT(A1,"0"),Sheet2!A:B,2,0)

 

[Orlando Magalhães Filho]

Hi Dianna,

This code below does what you'd do with F2+Enter


Sub TextToNumberOnActiveColumn()
Dim r As Integer
Dim c As Range
r = ActiveCell.EntireColumn.Range("A1").CurrentRegion.Rows.Count
ActiveCell.EntireColumn.Range("A1").Resize(r, 1).NumberFormat =
"#,##0.00"
For Each c In ActiveCell.EntireColumn.Range("A1").Resize(r, 1).Cells
If IsNumeric(c.Value) Then c.FormulaR1C1 = c.Value
Next
End Sub


HTH