Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text vs Number Lookup Issue (Excel 2002)

    Hi All!

    Usually working in Access, and feeling a little cut off without search *g*, I'm hoping someone has an easy answer....

    I'm using a query to pull down data in MS Query from DB2, one field of which is a number, but in DB2 it is text. In Access I can convert it to an integer (cint), but I don't seem to be able to find an equivalent thing in Excel.

    The reason why I'm trying to convert it to an integer is that I have a lookup table, and I want the numbers (1 thru 5) to be looked up in the lookup table. When the text data is pulled into excel, it doesn't matter whether the cell is formatted for text or number, it won't lookup the number. If I were to format the cells as integer and then overtype all the numbers, it's fine. Same if I want to go text all the way. But simply formatting the cells doesn't work without the overtype step, which just isn't practical.

    I've also tried pulling them into a different column using a formula, no luck. Has anyone had this issue before, and what's the simplest way to get out of this mess?

    TIA!

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text vs Number Lookup Issue (Excel 2002)

    If this is something that you have to do frequently, then you probably should create a macro to do it (we'll be glad to help if you need it). I*f you need to do this infrequently, then try this:

    1- Find an empty cell in the worksheet that is not formatted as text and enter a 1 into this cell.

    2- Select the cell with the 1 in it and select Copy from the Edit menu to copy the 1.

    3- Select the cells with the data that should be numbers and then select Paste Special from the Edit menu.

    4- In the resulting dialog box click on Multiply in the Operation section. Click on OK.

    The data should now be converted to numbers and you can delete the 1 from the cell where you put it.
    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text vs Number Lookup Issue (Excel 2002)

    When you create a new database query, the last step in the Query Wizard gives you the option
    (1) View the data in Excel
    (2) View the data or edit the query using MS Query
    (3) Create an OLAP cube
    Select the second option, then click the SQL button (or select View | SQL). You can now add CInt( ) around the text field. Click OK to confirm the SQL, then quit MS Query and return the data in Excel.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text vs Number Lookup Issue (Excel 2002)

    Well, I was trying to automate it, since it happens every quarter. Used to be manual, now here I go, Access person, getting all fancy.

    I found the solution: since the data in this case is simple, I went to a new column and found =Int(g6) would fool the cell into thinking it had an integer.

    But now I have a question about VLookup. This might warrant another discussion, but maybe not?

    I thought the way that VLookup was supposed to work was to look for the closest possible value. The second part of my task is to lookup asset amounts and categorize them. In this case, the field is a long integer, and it appears to be downloading properly.

    My lookup looks like this:

    <table border=1><td>Assets (<)</td><td>Label</td><td>50,000</td><td><=50</td> <td>100,000</td> <td>>50 to 100</td><td>500,000 </td> <td>>100 to 500</td><td>1,000,000,000 </td> <td>>500</td></table>

    My Formula: =VLOOKUP(G6,Lookups!$C$3:$D$8,2)
    But for some odd reason, the asset amount of 315,000 is pulling the label >50 to 100; actually none of the values are returning properly. Am I missing something here?



    Thanks!!!

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text vs Number Lookup Issue (Excel 2002)

    Hi Hans,

    That was the very first thing I tried, early last week. MS Query didn't like it. I don't recall the error that I got last week, but today I'm getting errors like my names are too long, maximum length is 8 (I didn't get this error last week!). I couldn't even put aliases to the field names. From some other problems I've seen, my best guess would be that it's a driver issue, but getting those gov't info resources people to fix a user issue...well, let's just say I'd have a better time fixing the problems myself. Ugggh.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text vs Number Lookup Issue (Excel 2002)

    Hi Cecilia,

    Perhaps the ODBC driver for DB2 doesn't support functions like CInt. I can't test it, not having access to a DB2 database...
    (The driver for Access does support many VB functions)

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text vs Number Lookup Issue (Excel 2002)

    Well, the only experience I can tell you about is that it did when we were using 95/98 and Excel 97. The reason I agree that it's an ODBC driver issue is that in Access, there are certain tables that certain parameters don't work on when trying to view a select query. But then if you do a make table query, it works fine.

    Uggggh <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text vs Number Lookup Issue (Excel 2002)

    VLookup by default looks for the first value that is less than or equal to the search value. So you must shift the second column with respect to the first one:

    <table border=1><td>Assets</td><td>Label</td><td align=right>0</td><td><=50</td><td align=right>50000</td><td>>50 to 100</td><td align=right>100000</td><td>>100 to 500</td><td align=right>500000</td><td>>500</td></table>

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text vs Number Lookup Issue (Excel 2002)

    Thanks! Boy, I can be dumb sometimes. That worked like a charm!

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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