Convert a range of numbers to strings

[Curious]

Download a list and past as value. Because vlookup function demands
text format, I need to convert the numbers into strings. If I loop the
list, and convert the cells one by one, it works, but takes a long
time. I am trying to convert the whole range with no luck.

I tried something like
Selection = format (selection,”@”)
Named range =format (named range,”@”)

Even manually select the range--> format cells-->Category:Text, still
didn’t work.

I use the function TYPE to monitor the cell's type. After I think I
get the format changed, the TYPE continues to show “1”.

Interestingly enough, after I reformat a cell, if I retyped the
number, it suddenly worked, that is, my vlookup function suddenly
recognized the number as text.

My questions are
(1) Why formatting a cell does not take immediate effect with a
vlookup function or with a type function?
(2) How can I convert the whole range into string?

Thank you in advance.

 

[James Ravenswood]

Download a list and past as value. Because vlookup function demands
text format, I need to convert the numbers into strings. If I loop the
list, and convert the cells one by one, it works, but takes a long
time. I am trying to convert the whole range with no luck.

I tried something like
Selection = format (selection,”@”)
Named range =format (named range,”@”)

Even manually select the range--> format cells-->Category:Text, still
didn’t work.

I use the function TYPE to monitor the cell's type. After I think I
get the format changed, the TYPE continues to show “1”.

Interestingly enough, after I reformat a cell, if I retyped the
number, it suddenly worked, that is, my vlookup function suddenly
recognized the number as text.

My questions are
(1) Why formatting a cell does not take immediate effect with a
vlookup function or with a type function?
(2) How can I convert the whole range into string?

Thank you in advance.

1. reformating a cell does not change its contents
2. try:

Sub FixThem()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each r In Selection
With r
st = .Text
.Clear
.NumberFormat = "@"
.Value = st
End With
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Pete_UK]

You could select those cells, click on Data | Text-to-Columns, then
click Finish.

Another way is not to change them but to change your formula,
something like this:

=VLOOKUP(A1&"",lookup_table,col,0)

Hope this helps.

Pete