Formula Help

[alwaysXcel]

Hello All,

The formula I am using below does not work properly, I don't know if something is wrong with the formula or the lookup string? Please help!!!

Cell A1

=LEPICTOTALEAcctNo5088BOYTOTAL34890545CYTOTAL-13348132PYTOTAL-24856934XYTOTAL-18994715BRTOTAL-5260505XRTOTAL-549584

Cell A2

==LOOKUP(10^308,--MID(SUBSTITUTE(SUBSTITUTE((A1),"XRTOTAL",REPT(" ",100)),"XRTOTAL","$$"),100,ROW(INDIRECT("1:100"))))

The result is giving me a 5 instead of -549584


Please help!!!!!!!

[Zack Barresse]

Hi there,


How about some more details.

What is the value in A1? What does it represent? Where does it come from?
What is it you are trying to accomplish with that formula?
Why should it return -549584?
Can you post an example spreadsheet?

[Ken Wright]

Your first substitute has already taken out the XRTOTAL piece, so your second substitute fails because it tries to do the same, but that element of text is now gone.

That having been said, if all you are looking to do is try and get the last number after the final minus sign then you can use

=--MID(A1,FIND("[",SUBSTITUTE(A1,"-","[",LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))),LEN(A1))

If you want it returned as a text string then just lose the double unary conversion at the beginning

Regards
Ken......................

[alwaysXcel]

Here is an attached sample worksheet. As you can see, its not picking up the last set of digits.

[Jacob Hilderbrand]

I just tried it with Ken's formula and it seems to be working just fine. I get -455549584. Is that not the desired result?

[alwaysXcel]

You're right, thats exactly what I am looking for. But how can i standardize this for other variables in the string such as...BOYTOTAL,CYTOTAL,PYTOTAL...etc??

Thank you very much for all your help!!

[Ken Wright]

Ahhhhhh - I see (I think)

is that the structure of every record? eg

BOYTOTAL / num / CY.. / num / PY.. / num / XY.. / num / BR.. / num / XR.. / num

If so then seeing as you are editing each formula to create 6 different formulas anyway you could simply use:-

=MID(A1,FIND("OYTOTAL",A1)+7,FIND("CYTOTAL",A1)-FIND("OYTOTAL",A1)-7)
 
=MID(A1,FIND("CYTOTAL",A1)+7,FIND("PYTOTAL",A1)-FIND("CYTOTAL",A1)-7)
 
=MID(A1,FIND("PYTOTAL",A1)+7,FIND("XYTOTAL",A1)-FIND("PYTOTAL",A1)-7)
 
=MID(A1,FIND("XYTOTAL",A1)+7,FIND("BRTOTAL",A1)-FIND("XYTOTAL",A1)-7)
 
=MID(A1,FIND("BRTOTAL",A1)+7,FIND("XRTOTAL",A1)-FIND("BRTOTAL",A1)-7)
 
=MID(A1,FIND("XRTOTAL",A1)+7,LEN(A1))

Regards
Ken.................

[alwaysXcel]

Also, the formula gives me #VALUE when the number is not negative.

[Ken Wright]

As long as the basic structure of each record is the same, ie in the format I outlined above, and the order of the OY/CY/PY/XY/BR/XR elements doesn't change then the formulas I posted should do. If you want the values returned as numeric then just use the double unary operator after the = sign to convert the returned strings.

Regards
Ken..................