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

Try this in B3 and drag down

=IF(ISERROR(FIND("CR",A3)),SUBSTITUTE(SUBSTITUTE(A 3,"DB",""),CHAR(160),"")*1,SUBSTITUTE(SUBSTITUTE(A 3,"CR",""),CHAR(160),"")*-1)

Thank you. Nice solution. I've applied it and works great.
Appreciate your help.
I especially like the use of the ISERROR function.

Many thanks for the 3 solutions. Each work well. How can I mark this as "Solved"?

Near the top of page, it's under Thread Tools.