Performing arithmetic functions to cells with number and text

  • Thread starter Arithmetic functions with embedded text
  • Start date
A

Arithmetic functions with embedded text

I have data entered in one cell in this format: 80/60

and I would like to find the difference of that cell with one of similar
format...75/57

by subtracting 75-80 and 57-60.

How can I do this with the / embedded within the cell.

Perhaps just entering the data with a "," instead would help?
 
D

Dom_Ciccone

If all your fractions have the same number of digits for the numerators and
the same number for the denominators (in your example both numerators are 2
digits and both denominators are 2 digits), you can use the Left and Right
commands.

If fraction 1 is in cell C2 and fraction 2 in cell D2 then a formula like:

=INT(LEFT(C2,2))-INT(LEFT(D2,2)) & "/" & INT(RIGHT(C2,2))-INT(RIGHT(D2,2))

would do the job.

If your fractions have different digit lengths, such as 80/160 - 13/10 then
you will need to use the Find command to locate the "/" symbol. This makes
the formula:

=INT(LEFT(C2,FIND("/",C2)-1))-INT(LEFT(D2,FIND("/",D2)-1)) & "/" &
INT(RIGHT(C2,LEN(C2)-FIND("/",C2)))-INT(RIGHT(D2,LEN(D2)-FIND("/",D2)))

Hope that helps

DC
 
M

Max

Assuming in A1: 80/60, in B1: 75/57,
then in C1:
=(LEFT(B1,SEARCH("/",B1)-1)-LEFT(A1,SEARCH("/",A1)-1))&"/"&(MID(B1,SEARCH("/",B1)+1,99)-MID(A1,SEARCH("/",A1)+1,99))
C1 returns: -5/-3, which is the presumed result that you're after
 
M

Mike H

These 2 may be of use:-


Divides the bits after the /
=MID(A1,(FIND("/",A1,1)+1),99)/MID(B1,(FIND("/",B1,1)+1),99)

Divides the bits before the /
=LEFT(A1,((FIND("/",A1,1))-1))/LEFT(B1,((FIND("/",B1,1))-1))

Mike
 
Top