Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IF Function with both text and number (excel 2000 SP1)

    Hi everyone, I need to compare one column to the next using an IF function same row, column G and H the value will be both text and number (ie. P83250S0X A200XA) I can not get a reliable return. I have tried ISTEXT, and ISNUMBER, but no luck, Any help?
    Thanks in advance

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

    Re: IF Function with both text and number (excel 2000 SP1)

    What do you want to accomplish? If you want to test if the values in G and H are equal, you can use G1=H1 as test, e.g.

    <code>=IF(G1=H1,"Equal","Not equal")</code>

  3. #3
    New Lounger
    Join Date
    Dec 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Function with both text and number (excel 2000 SP1)

    Hi Hans, I am still trying to get the Bar code scanning operation to work.
    I have tried =IF(ISTEXT(H2),IF(G2=H2,"",Oodles()))
    and =IF(G2=H2,1,2) Where 2 under conditional format turned the cell red, and 1 turned the cell green
    I am at a loss .
    Thanks for the quick reply

  4. #4
    New Lounger
    Join Date
    Dec 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Function with both text and number (excel 2000 SP1)

    Nope tried that, and still showed Not Equal, when it matches

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

    Re: IF Function with both text and number (excel 2000 SP1)

    Could you attach a small sample workbook so that we can see what you're working with? Remove all sensitive data, or replace it with dummy data.

  6. #6
    New Lounger
    Join Date
    Dec 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Function with both text and number (excel 2000 SP1)

    Yes,, carriage return set to right,, only active to row 11 (file would be 9 meg otherwise)
    Thanks again Hans.

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

    Re: IF Function with both text and number (excel 2000 SP1)

    A workbook with only 11 rows is fine, that should be sufficient for us to test with, but column G contains #VALUE and column H is blank, so there is nothing to compare <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  8. #8
    New Lounger
    Join Date
    Dec 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Function with both text and number (excel 2000 SP1)

    Okay, I have inserted some data, again, this is coming from a bar code scanner,, in column G where it is =D2 I have tried to use
    Range("H1:H500").Value = Range("G1:G500").Value
    with another column inserted and all of the change events moved one row over, but that didn't work either (it copied it correctly, but didn't see the match)
    In Column B I am only pulling data over after the "&" (a verify type) into column C, then doing a VLOOKUP to get the Container number.

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

    Re: IF Function with both text and number (excel 2000 SP1)

    The values in columns G and H contain different numbers of trailing spaces, that's why seemingly matching values don't. Testing on TRIM(G2)=TRIM(H2) etc. should work.
    Your Oodles function is extremely irritating, by the way.

  10. #10
    New Lounger
    Join Date
    Dec 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Function with both text and number (excel 2000 SP1)

    Thanks Hans, so I can use the ISTEXT with this function "=IF(ISTEXT(H3),IF(TRIM(G3)=TRIM(H3),"",Oodles())) " ?
    I will try that out,, it is more irritating when you have the "laser.wav" file in the root of C: :-)
    Thanks again Hans

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: IF Function with both text and number (excel 2000 SP1)

    Or

    =IF(ISTEXT(H3),IF(TRIM(G3)=TRIM(H3),"",Oodles(I3)) ,"")

    with this change to your function:

    Function Oodles(rng As Range)
    ' set value of function to be returned
    Call sndPlaySound32("C:laser.wav", 0)
    ... etc....
    Msgbox "Labels Do Not Match in Row " & rng.Row
    ' call macro
    Call Error
    End Function

    Your function would probably be faster if you rewrote it under the Worksheet_Change Event.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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