Convert to Num only then Subtract 4.00 (cm) from 2.70 (cm)

[harkirat777]

Hi

I have two columns with the data like below:

4.00 (cm) 2.70 (cm)
2.20 (cm) 2.20 (cm)
4.00 (cm) 4.00 (cm)
2.20 (cm) 2.20 (cm)
3.00 (cm) 4.60 (cm)
4.50 (cm) 4.50 (cm)
4.60 (cm) 3.00 (cm)
2.90 (cm) 2.90 (cm)
5.00 (cm) 5.00 (cm)


Can you please help me:
1) I want to extract numbers from these columns to another two columns

2) Then I want to subtract two columns to get the distance.

Thanks a ton
Harkirat

[ben_hensel]

Assuming:
A1 = "4.00 (cm)"
B1 = 2.70 (cm)"

Then:
C1 = VALUE( LEFT(A1, FIND("(", A1, 1) - 1) )
//Find the left paren, clip off everything from there to the right, convert what's left to a number,
D1 = VALUE( LEFT(B1, FIND("(", B1, 1) - 1) )
//Ditto for the second column

E1 = ABS(C1 - D1)
This will return the absolute value of the difference, so it will always be positive, ie
abs(2.7 - 4) = 1.3 not -1.3

[Pete_UK]

Another way of converting the numbers in C1:

=SUBSTITUTE(A1,"(cm)","")*1

Copy into D1, then copy both down.

Hope this helps.

Pete

[benishiryo]

hi Harkirat. try:
=--LEFT(A1,FIND(" ",A1)-1)

you can just take the above result minus the other after copying the formula

[harkirat777]

Thanks a lot guys it worked!
I liked Pete's formula more, short and simple.

[ben_hensel]

You can copy it down.

Easiest way... select the cell C1,hover the cursor over the bottom right corner so it turns into that little all-black cross, then double-click. The column will fill down each row as long as there's something to the left of it.

...If it was me, I would also do a dummy column that did
=ISERROR(SEARCH(A1,"cm",1)+SEARCH(B1,"cm",1))
and then filter for "TRUE" to check for unit clashes.