Rectifying Value Error in Excel to find percentage by extracting dividend/divisor from a single cell having variable length.

[Asad Mir]

Hi there!


I am having a problem in finding percentage.

In cell B3 I have a text-number string like "Science580/1050", "Arts550/1050", etc respectively. To find percentage by dividing 580 by 1050 I developed a formula copied below. Please note that in above example i.e. "Science580/1050" Line break is inserted right after the word science so i used "char(10)" in the formula. The following function is correctly extracting the numbers 580 and 1050 and divides them using "/" arithmetic operator but regretfully it shows "#Value" error.

=MID($B3,FIND(CHAR(10),$B3,1),FIND("/",$B3)-FIND(CHAR(10),$B3))/RIGHT($B3,LEN($B3)-FIND("/",$B3))


Any help to remove this error and finding percentage using the same formula would highly be appreciated. Anticipating your positive reply. Thank you all.


Regards

 

[JackDanIce]

Welcome to the board. Try:

=VALUE(MID($B3,FIND(CHAR(10),$B3,1),FIND("/",$B3)-FIND(CHAR(10),$B3)))/VALUE(RIGHT($B3,LEN($B3)-FIND("/",$B3)))

 

[Asad Mir]

Thanks for replying. I tried it already, it is useless. same error.

 

[FormR]

Hi, you are returning the line feed in the first MID() function - try this slight adjustment.

=MID($B3,FIND(CHAR(10),$B3,1)+1,FIND("/",$B3)-FIND(CHAR(10),$B3)-1)/RIGHT($B3,LEN($B3)-FIND("/",$B3))

 

[Asad Mir]

OMG. Thanks Aloooooooooot. Problem solved. Thanks Thanks Thanks alot. Could you please explain a little about line feed.

 

[FormR]

Glad it helped

Could you please explain a little about line feed.

The Char(10) - so your MID() function returned "Char(10)580" which when divided by "1050" caused the #VALUE error.

 

[Peter_SSs]

.. or with a few less function calls:

=REPLACE(LEFT(B3,FIND("/",B3)-1),1,FIND(CHAR(10),B3),"")/REPLACE(B3,1,FIND("/",B3),"")

 

[Asad Mir]

Thanks Alot.... Sir!