Comparing a number without dimensional units with one that has dimensional units?

[InOverMyH3ad]

Scenario:

Cell A: 12
Cell B: 12" (or even 12 in.)

Cell A will never have units. It will always be cell B that will be the issue. I'm trying to figure out a way to be able to compare the two cells and determine if they are equal or what the difference between them us. The issue is getting excel to ignore the units after the numbers. Is this possible in some sort of non-VB way?

 

[hagia_sofia]

Hello, could you please first share with us what version of Excel are you using?

 

[InOverMyH3ad]

Hello, could you please first share with us what version of Excel are you using?

Windows 11, file is an .xlsx type

 

[hagia_sofia]

I see. So probably Excel 2021 or 365?

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[hagia_sofia]

Please test the following for a number with additional signs:

--(CONCAT(IFERROR(--(MID(B1,SEQUENCE(LEN(B1)),1)),"")))

 

[InOverMyH3ad]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Got it. Thank you.

 

[InOverMyH3ad]

Please test the following for a number with additional signs:

--(CONCAT(IFERROR(--(MID(B1,SEQUENCE(LEN(B1)),1)),"")))

To start, thank you for your help. This gets me close, but I didn't realize there might be a situation where I have something like 16.5 in or 16.5". The code you graciously provided is giving me 165 as an output for that. It's doing too good of a job removing punctuation.

 

[hagia_sofia]

Many thanks, yeah, my bad for not considering that option. Another attempt:

=LET(
a,B1,
b,LEN(a),
c,ISNUMBER(--(MID(a,SEQUENCE(b),1))),
d,SEQUENCE(b),
e,IF(c*d=0,"",c*d),
MID(a,MIN(e),MAX(e)-MIN(e)+1))

 

[Cubist]

Another option:

Book1
	A	B
2	12"	12
3	12 in	12
4	12.5 in	12.5
5	16.584 in	16.584
6	16.582 "	16.582
7	It's 3.5 inches long	3.5
Sheet3

Cell Formulas
Range	Formula
B2:B7	B2	=LET(t,TOCOL(SEARCH(SEQUENCE(10,,0),A2),2),--MID(A2,MIN(t),MAX(t)-MIN(t)+1))