Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert a range of numbers to strings
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert a range of numbers to strings
On Aug 12, 10:35*am, Curious 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert a range of numbers to strings
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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert a 8X3 table to a vertical range of the Unique strings only | Excel Worksheet Functions | |||
convert a range of text into numbers | Excel Programming | |||
Convert a range to a 2sequence of numbers? | Excel Worksheet Functions | |||
Convert values to strings | Excel Programming | |||
Convert String of 512 numbers to a range | Excel Programming |