Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2003
    Location
    48150
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Number to Text problem

    I have a vlookup formula in column C that is referencing a number stored as text in column A against a table that also has numbers as text (thousands of lines)

    I've converted my main worksheet with the formulas to text and now must go through and hit F2 and Enter (basically touch every line) to get the vlookup formula to recognize the text and return a the value in the table.

    I've run into this before and was provided some VBA code that could address the entire row very quickly.

    Any help?

    Thank you,

    JG

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    select a blank cell and set it to the format you want the row to be in
    copy the cell
    select the cells to convert
    Paste-special - add

    They will all be converted to the new format and be numbers - no VB required

    Steve

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Further to Steve's answer, another way to compare 'apples' with 'apples' is to adjust your vlookup formula.
    =Vlookup(what, where, fetch,lookup type)
    If the thing you are looking up is a number stored as text, and you are matching it against a column where the numbers are 'numbers', use =Vlookup(what+0,where,fetch,lookup type).
    If the thing you are looking up is a number stored as number, and you are matching it against a column where the numbers are stored as text, use =Vlookup(""&what,where,fetch,lookup type).

    Steve's answer is probably best.

    zeddy

  4. The Following User Says Thank You to zeddy For This Useful Post:

    jgellert (2012-03-03)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •