A special calculation

[volabos]

Hi, I am trying to do a special arithmetic calculation however entries for that calculation is not number !!! My problem is as follows :

in cell "A1" I have : ="XX + "& 0.3 and,
in cell "A2" I have : ="XX + "& 0.5

Now I want to subtract "A2" from "A1" and i.e. I want, =A2-A1 should give 0.2
Is it possible to do that? Can anyone suggest me any way out?

Regards,
:help

[Paul_Hossler]

Why do you have "XX +" & 0.3 in A1? It sort of looks like it's an algebraic expression.

A1 .3
A2 .5
A3 = A2-A1

will give .2

Paul

[Bob Phillips]

Try

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

[mikerickson]

This will subtract the two numeric parts

=VALUE(MID(A1&IF(ISNUMBER(FIND(".",A1)),"",".")&"0",FIND("+",A1&"+0"),100))
- VALUE(MID(A2&IF(ISNUMBER(FIND(".",A2)),"",".")&"0",FIND("+",A2&"+0"),100))

If you might be dealing with values like XX-08, this formula will work

=VALUE(MID(A1&IF(ISNUMBER(FIND(".",A1)),"",".")&"0",IF(ISNUMBER(FIND("+",A1)),FIND("+",A1),FIND("-",A1&"-0")),100))
- VALUE(MID(A2&IF(ISNUMBER(FIND(".",A2)),"",".")&"0",IF(ISNUMBER(FIND("+",A2)),FIND("+",A2),FIND("-",A2&"-0")),100))

[volabos]

Hi Mikerickson and xld thanks for you answer. However can you explain those functions little bit, and the logic as well, so that I can understand that?

Regards,

[Aussiebear]

Volabos, do you have Excel Help installed on your computer?

[mikerickson]

My formula keys on the + (or -) and converts what follows to a number.
It includes a fair amount of possible situational handling (it will handle "xx+30", "samuel-5.23", etc.). If your data is validated to fit a single format (eg. will the entry ALWAYS have a decimal point?) it could be simplified.

[Bob Phillips]

Mine is very flexible. It searches for the first numeric character, and then builds an array of every string that starts from that first character. It then lookups a very big number, 99^99, into that array which will find the last numeric element.

So it can handle

- a pure number - 123
- leading text - abc123
- trailing text - 123abc
- leading and trailing text - abc123xyz

If you have two numbers, such as 123abc456 it reruns the first.

[Bob Phillips]

Volabos, do you have Excel Help installed on your computer?

I wouldn't bet on help shedding much light on those two beasties :)