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?
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
Steven,

Maybe...
Excel 2007
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">231</td><td style="text-align: right;;">231</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">231A</td><td style="text-align: right;;">231</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">231</td><td style="text-align: right;;">254</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">231A</td><td style="text-align: right;;">231</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">234.0</td><td style="text-align: right;;">234</td><td style="text-align: right;;">TRUE</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=IFERROR(<font color="Blue">1*A1=1*B1,FALSE</font>)</td></tr></tbody></table></td></tr></table><br />

<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,069
Office Version
  1. 2013
Platform
  1. Windows
Steven,

You are welcome.

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

Watch MrExcel Video

Forum statistics

Threads
1,122,817
Messages
5,598,275
Members
414,221
Latest member
MD1222

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