Compare Mixed Number, Number stored as text, alphanumeric

StevenHPerry

New Member
Joined
Jul 17, 2009
Messages
3
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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,561
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Steven,

Maybe...
Excel 2007

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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>


Hope that helps.
 

StevenHPerry

New Member
Joined
Jul 17, 2009
Messages
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.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,561
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Steven,

You are welcome.

So 231.0= 231.0 => False ???
231(text) = 231(text) => False ????
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,008
Messages
5,834,852
Members
430,324
Latest member
bosphoruskid

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top