Breaking down a number into its correct format

[sungen99]

So i am receiving data from a data feed but excel is not interpreting the data correctly so i am seeking some assistance on how to fix it.


A cell could be this "=-43-6"

What i actually need to to show me is: -43.75


Another example: a cell could be this "=15-2"

What i need it to show me is: 15.25



What i can tell is that for a negative number there is a "-" after the equal sign but there is nothing there if its a positive number.

Next is the whole number the the "-XX" after... the only options there would be:
-0 is .00
-2 is .25
-4 is .50
-6 is .75

this is the only options

thank you for the help.

[MrShorty]

So we basically have a number that is (+/-)xx-y(/8). I would probably use text manipulation to get the text string into xx.y format, then use the DOLLARDE() function to convert the fractional (1/8th) dollar value to the corresponding decimal value:

1) RIGHT() function to extract the unsigned number RIGHT(text,4)
2) SUBSTITUTE() function to change the - to . =SUBSTITUTE(RIGHT(...),"-",".")
3) VALUE() [or equivalent] to convert the number as text to a number VALUE(SUBSTITUTE(...))
4) Check the 2nd character of the original string to see if it is "-" and multiply the result by -1 if it is: IF(MID(text,2,1)="-",-1,1)*VALUE(...)
5) Finally, nest all of that inside of the DOLLARDE() function to get the final result =DOLLARDE(IF(...)*VALUE(...),8)

I am assuming that the only variation in the text strings is the presence/absence of the first "-" character that indicates negative/positive values. Otherwise, the text strings are always the same -- two integer characters and one decimal character. If there can be 3 integer characters or there are other possible variations in the length of the text string, those other variations are not accounted for by this.

[sungen99]

MrShorty.

This is really amazing but i am having a heck of a time making the steps give me what i need. I have uploaded an example, perhaps you could help just bit more to bring this home for me.

[MrShorty]

I must have misunderstood. I thought you had "=-43-6" as a text string, but those are actually formulas.

The same strategy should work, but you need to use the FORMULATEXT() function to extract the formula as a text string. My version of Excel does not support the FORMULATEXT() function. You should just need to substitute FORMULATEXT(cell) where I have text in my description:

A14 should be something like =DOLLARDE(IF(MID(FORMULATEXT(A3),2,1)="-",-1,1)*VALUE(SUBSTITUTE(RIGHT(FORMULATEXT(A3),4),"-","."),8) [hopefully I got the parentheses all paired up correctly].

[sungen99]

Son of a gun. no joy. well thank you anyway for trying to help me. perhaps i can use multiple lines to ultimately get what i need. if it takes 5 cheater cells to make it work so be it.

[MrShorty]

Break out the steps into multiple cells and which one doesn't work:

A16 =FORMULATEXT(A3) [Should be the text string =-16-4]
A17 =RIGHT(A16,4) [Should be the text string 16-4]
A18 =SUBSTITUTE(A17,"-",".") [Should be the text string 16.4] Note here that I am assuming "." as the decimal separator -- any chance you are using "," or other character for a decimal separator?
A19 =VALUE(A18) [Should be the number 16.4]
A20 =IF(MID(A16,2,1)="-",-1,1) [Should be the number -1]
A21 =A20*A19 [Should be -16.4]
A22 =DOLLARDE(A21,8) [Should be the number 16.5]

Which step does not give the expected result?

[protonLeah]

You could do a find/replace the equal sign with nothing then use:

Please Login or Register  to view this content.

[sungen99]

Ben this is great, just ONE thing left.....

the change column formula. B15. B3 is showing 0-2 that is actually 0.25 positive. It looks like all the other cellls work great. How would you alter B15 to account for this situation? a few more examples if that would help to understand:

0-2 would be a positive 0.25
-0-2 would be -0.25

5-4 would be a positive 5.50

-47-6 would be -47.75

THANK you SO much for helping here.

[protonLeah]

maybe:

Please Login or Register  to view this content.

but, this requires that the equal sign be replaced with a single quote (').

[sungen99]

This looks to be working.. thank you guys!