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

1. 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?

2. 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. 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 .

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

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

5. 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. 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. 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. 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. 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. 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. 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.

Posting Permissions

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