# Compare Mixed Number, Number stored as text, alphanumeric

#### StevenHPerry

##### New Member
How can I check cells for equality if they are variably a number, a number stored as text, and/or an alphanumeric?

My data could be any of these
231.0
231 (as text)
231A

The numeric portion will always be 3 digits (201-299), but can be stored as number or text. The optional alpha character will always be (a-z) or (A-Z).

My desired result is
231.0 = 231 (text) => True

231A = 231.0 (number) => False
231A = 231 (text) => False

And obviously if the numeric portion is different, they should not evaluate to true either.
235 = 231 => False
235A = 231A => False
231.0 = 299A => False

I've tried casting all items to text using for instance "If(IsNumber(231.0),Text(231.0,"###")" and then doing a straight comparison and also as a subtraction not equal to zero.

I'm stumped on this one... but maybe the answer is obvious to someone else?

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Steven,

Maybe...
Excel 2007

Excel 2007
ABC
1231231TRUE
2231A231FALSE
3231254FALSE
4231A231FALSE
5234.0234TRUE
Sheet1
Cell Formulas
RangeFormula
C1=IFERROR(1*A1=1*B1,FALSE)

</tbody>

Hope that helps.

Code:
``=left(a1,3)=left(b1,3)``

Thank you both for your very quick replies. Tony's IfError function almost did the trick. Two more criteria I forgot to post...

231A = 231B => False
201A = 299A => False

So I added a quick if(A1=B1) check and nested the iferror in the false condition. This seems to be working on all occurrences.

Thanks for the breakthrough! I'm off and running again.

Steven,

You are welcome.

So 231.0= 231.0 => False ???
231(text) = 231(text) => False ????

Replies
6
Views
763
Replies
13
Views
23K
Replies
5
Views
2K
Replies
1
Views
288
Replies
0
Views
8K

1,203,242
Messages
6,054,343
Members
444,717
Latest member
melindanegron

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back