Convert a text string with a leading dash to a negative number

Question

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 a hyphen; it may be an em dash
My apologies for the confusion... I'm now wondering what sort of programmers might use an em dash instead of a minus 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?

Answer 1

I think I've found a simple solution. In one of the cells with the leading em-dash, highlight and copy just the em-dash. Using Excel's "Find and Replace" function, paste the em-dash in the "Find what" block, then type the minus sign into the "Replace with" block. Apply the Replace to all cells you wish to convert.

Answer 2

If cell A1 contains text that is in the format "$123.45" or "-$543.21", you can use the following formula to get the numerical value.

=IF(LEFT(A1,1)="-",0-VALUE(RIGHT(A1,LEN(A1)-1)),VALUE(A1))

If the text starts with a "-", this will get the value of everything after the "-" and make it negative.

Answer 3

I'd recommend:

1. Extract value after $ sign, do not use fixed position
2. Check the value sign by checking a symbol immediately before $ sign.

So the formula may be

=IF(IF(ISERROR(FIND(MID(A1,FIND("$",A1)-1,1),"<dash><hyphen>")),3,FIND(MID(A1,FIND("$",A1)-1,1),"<dash><hyphen>"))<3,-MID(A1,FIND("$",A1)+1,LEN(A1)),0+MID(A1,FIND("$",A1)+1,LEN(A1)))

where

"<dash><hyphen>" - a literal with possible negative signs (replace placeholders with proper chars in any order, so you obtain 2-symbol literal in shown case)

3 - the number which is greater than the number of chars in previous literal

Those values may be replaced with a reference to a cell which contains all possible "minus" signs (like Z1 and 1+LENGTH(Z1) respectively).

---

Update. Debugginig the formula.

Create a table on an empty worksheet.

Insert 4 different values to A column (without/with leading spaces, positive/negative).

Insert the below formulas:

Cell          Formula                      Meaning
-------------------------------------------------------------
$B$1          =IF(I1,-G1,0+G1)             Result
$C$1          =LEN(A1)                     Data length in source
$D$1          =FIND("$",A1)                $ position in source
$E$1          =MID(A1,D1-1,1)              symbol before $
$F$1          =FIND(E1,"<dash><hyphen>")   symbol before $ position in hyphens list
$G$1          =MID(A1,D1+1,C1)             number from source
$H$1          =ISERROR(F1)                 correction when no symbol before $ in list
$I$1          =IF(H1,3,F1)<3               define is a source positive or negative

Copy this formula pack to rows 2-4. Check you obtain output like this:

     A        B      C   D   E      F       G    H       I
1    $123     123    4   1   #VAL!  #VAL!   123  TRUE    FALSE   
2    -$234    -234   5   2   -      1       234  FALSE   TRUE    
3      $345   345    6   3          #VAL!   345  TRUE    FALSE   
4      -$456  -456   7   4   -      1       456  FALSE   TRUE    

Columns C-I contains single calculation steps, and column B contains the final formula with all substitutions.

Where is calculation failure?