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.

On Aug 12, 10:35*am, Curious <wise...@yahoo.com> wrote:
> 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

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

On Aug 12, 3:35*pm, Curious <wise...@yahoo.com> wrote:
> 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.