Removing brackets from values and converting to positive values in excel

Question

Anyone who can help me with the best way to remove brackets from values and letting it show as positive on Excel rows and columns?

I tried abs, but all my values remained negative and also removed the currency. I tried number formatting and it displayed as positive, but once you clicked on the value there's a negative sign showing in the formula bar.

Answer 1

Approach

1. Insert entirely new sheet (Sheet 2, say). New workbook would be even better.
2. Select all cells in this sheet, format as number.
3. Step 2 critical, so worth emphasizing: To satisfy yourself that you have indeed done this, go so far as to select specific formatting e.g. 'no decimal places', 'thousands separator' etc. (screenshot below)
4. Copy the column of troublesome figures and paste into a regular notepad (not notepad++, but regular notepad [windows]
5. Once pasted into notepad, copy from notepad and make absolutely sure that you paste as text. This will ensure no prior / stubborn formatting persists. Hereafter assumes you pasted from cell A1 downwards in sheet
6. B1=--A1, drag down as required.
7. Copy the column of newly formed data in col B, and paste special, values only in col C (starting C1, say).
8. Select/highlight col C and see if Excel taskbar stats show the sum you're expecting (to enable if not already, right click taskbar and select as req.).

This should do the trick, assuming step 6 doesn't return any errors (if it does then you clearly have string characters; like currency etc., remove these from col A, and repeat Steps 5 & 6 until you have successfully removed such characters/are not getting any further errors in your plight.

---

You can manipulate the 'number formatted' text in col C as desired!

Highlight col C

This step may not be necessary, if you skip it and still encounter errors, makes sure to try again from scratch and include accordingly:

a couple of the options in the number format category (e.g. no decimal places, include ',' as thousands separator)

Copy values you're trying to fix and paste special values in Sheet 2, from cell A1 down (say).

Select all cells in the newly Set B1 = --A1.

In an adjacent column to your newly pasted cells, use a function = --A1 for the leading c