I have numbers in a large Excel sheet. The negative numbers are displayed as strings such as (-101.10) corresponding to -101.10. The positive numbers are displayed as usual, such as 101.10.
The problem is, Excel doesn't recognize the combination of both parentheses and the negative symbol.
How can I reformat these negative numbers so that Excel understands?
51 Answer
I’ll assume that you know where the (possibly malformed) numbers are.
For example, they might all be in Columns A and B,
having been imported from some external source,
while other columns have formulas, text, or numbers that you have entered.
Pick some area of the workbook that is the same size and shape.
In the example above,
you would need to pick two columns that aren’t being used for anything else.
You could use Columns G and H, or X and Y,
or you could go to another sheet.
Assuming that your data are in A and B, and G and H are available,
enter the following in Cell G1:
=IF(AND(LEFT(A1,2)="(-",RIGHT(A1,1)=")"), IFERROR(VALUE(MID(A1,2,LEN(A1)-2)), "ERROR: "&A1), A1)Drag/fill right to Column H and down to cover all your data.
Then copy and paste values back into Columns A and B.
Note that this will change (-number)to -number,
but not affect parentheses in any other context.
