Compare Mixed Number, Number stored as text, alphanumeric

[StevenHPerry]

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?

 

[Snakehips]

Steven,

Maybe...
Excel 2007

Excel 2007 A B C 1 231 231 TRUE 2 231A 231 FALSE 3 231 254 FALSE 4 231A 231 FALSE 5 234.0 234 TRUE Sheet1 Cell Formulas Range Formula C1 =IFERROR(1*A1=1*B1,FALSE)

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Hope that helps.

 

[NeonRedSharpie]

=left(a1,3)=left(b1,3)

 

[StevenHPerry]

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.

 

[Snakehips]

Steven,

You are welcome.

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