left sum

[Utsav]

Hi dear,

I have got two values
like this 63/59 innone cell and 18/11 in other cell.I want to add left values and right values ,I have done this using left function

=LEFT(C9,2)+LEFT(J9,2)

but the problem is some time the values is in single digit , then the formula stops working bcoz of this "/".

jus help me ...

Utsav

 

[Richard Schollar]

If the format remains the same ie 500dr or 500cr then you could extract the direction (dr/cr) via:

=RIGHT(TRIM(A1),2)

does this answer your query, or were you after a more generalised solution?

 

[Utsav]

like if I want to extract
5000from dips5000 or dips from disp5000.

like I don''t know the length of the text I want to extarct

In first case , I want to extract the numeric part in the other case I want to extract the alpahabetic part .

sorry to bother you again ...
but I m also trying to learn this trick ....

Utsav

 

[Richard Schollar]

Here's one way to lookup the numeric part (assuming the numbers are together and not separated by any alpha or other characters):

=-LOOKUP(0,-MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

Assuming you have the numeric extract in cell B1, then you can get the alpha only:

=SUBSTITUTE(A1,B1,"")

Hope thsi helps!

 

[Utsav]

Dear Richard,

Can u plz xplain me rhe whole formula , it will help me in understanding it and implementing ...

Regards
Utsav