CODEWITHSUNDEEP
excel-formula
David Kim
David Kim

Reputation: 91

Excel IF statement Comparing text with number

I was using IF statement to group some numbers together. In column A I have numeric and text values.

  A   B
1 s   =IF(A1>200,2345,"ad")

If I do this then the B1 returns 2345.

How does Excel compare string values with number values?

Upvotes: 9

Views: 23908

Answers (2)

Bobby Orndorff
Bobby Orndorff

Reputation: 3335

For the Excel compare operators (e.g. <, =, >), number values are less than text values which are less than logical values. You can use the VALUE function to convert text values to number values for number comparisons. You can use the TEXT function to convert number values to text values for text comparison.

Upvotes: 11

Mike
Mike

Reputation: 21

You can concatenate a letter to both the formula (string) and the number and excel will be able to compare them. I was having the same problem when comparing the result of an index match formula with a number and this solved it easily.

Example:

String: 

=CONCATENATE("Q",Result from formula)

Number: 

=CONCATENATE("Q",Number)

IF string=number should work now.

Upvotes: 2

Related Questions