The online broker I use must have the most dysfunctional website worldwide! They provide no "export" function, instead telling their users they can just copy data from tables rendered in their browser, and paste them into Excel. (Really!)
Surprisingly, Excel (2016, for Mac) can be goaded into converting the "positive text numbers" to actual numbers that can be operated on by the numerical functions (SUM
typically). However, the "negative text numbers" have defied all efforts I've found to be converted to a negative number.
A "positive text number" is: $123.45
A "negative text number" is: -$543.21 (note the leading text char hyphen
)
EDIT:
A "negative text number" is: – $299.55 (Discovered that leading character is NOT ahyphen
; it may be anem dash
My apologies for the confusion... I'm now wondering what sort of programmers might use anem dash
instead of aminus sign/hyphen
in a web page full of numbers, but that's a question for my online broker.
I'm able to convert a "positive text number" in cell A1 as follows: =VALUE(RIGHT(A1,6))
But this does not work on a "negative text number". It gets the number right, but loses the negative sign. I've tried changing the chars
value, but that doesn't help.
I've also tried the following without success:
1. multiplying by 1
2. text-to-columns
3. format cells...
Is there a way to do this - other than manually re-entering?
=VALUE(RIGHT(A1,6))
You extract a part from the middle of cell's value. The text is left-padded with spaces? If so is a text value with negative number value shifted right by a hyphen? Or a hyphen in it replaces a space on 4th position?