Original Nbr Desired Original Amount Desired Two decimal Place Number Result Two decimal Place Amount Result 9,825.00 DB 9,825.00



0.00 CR $0.00 9,830.00 CR (9,830.00)

4.12 CR ($4.12) 9,793.60 CR (9,793.60)

0.00 CR $0.00 9,898.00 DB 9,898.00

84.25 DB $84.25 9,897.46 CR (9,897.46)

0.00 CR $0.00 9,921.70 CR (9,921.70)

0.00 CR $0.00 9,925.30 DB 9,925.30

12.65 DB $12.65 10,008.00 DB 10,008.00

79.18 DB $79.18 18,786.60 DB 18,786.00

0.00 CR $0.00
18,999.54


$171.96

Sorry, Copy & Paste do not show up well here. Attached file will show problem better.

I have a spreadsheet with two columns. In column A are numbers with two decimals, column E contains amounts with two decimals.
My problem is the "CR" or "DB" with an unprintable character preceeding them.
I need to remove the text characters "CR" or "DB" and remove all spaces and unprintable characters.
I need to be able to add both resulting columns and get the results shown in cells B12 & F12 respectively.
I have tried experminting with TRIM, CLEAN & SUBSTITUTITE without less than needed results.

Sample CR_DB.xlsx