HELP! Need to sum columns where credit balances are shown as ###CR

A

ahwelch

I just got a huge trial balance from a client that shows debit amounts as
positive and credit amounts with a "cr" after the number. This makes it
impossible to sum! Looks like this...

2,000
5,000CR
3,000
2,000CR

How can I convert those CR amounts into negatives (either minus sign or
using parentheses). It would take days to convert each one, and the Format
function does not work, unless I'm missing a step. Please help ASAP. This
is due tomorrow!
Thank you!
 
J

Jacob Skaria

Assuming you have your numbers in Column A.

try in B1
=VALUE(SUBSTITUTE(A1,"CR",""))

Copy the formula down. Once done copy ColB-->paste Special-->Values..

If this post helps click Yes
 
J

Jacob Skaria

Assuming you have your numbers in Column A.

try in B1
=VALUE(SUBSTITUTE(A1,"CR",""))

Copy the formula down. Once done copy ColB-->paste Special-->Values..

If this post helps click Yes
 
A

ahwelch

It didn't work. Here is what I got (spacing is off a little, but you get the
picture)

200 200
50 50
70 70
250 250
13,156,231.93 13156231.93
291,987.01 291987.01
313.12CR #VALUE!
437.03CR #VALUE!

Any other thoughts?
 
A

ahwelch

It didn't work. Here is what I got (spacing is off a little, but you get the
picture)

200 200
50 50
70 70
250 250
13,156,231.93 13156231.93
291,987.01 291987.01
313.12CR #VALUE!
437.03CR #VALUE!

Any other thoughts?
 
T

T. Valko

2 step process...

Select the range of cells in question
Goto the menu Edit>Replace
Find what: CR
Replace with: -
Replace all

With the range still selected
Goto the menu Data>Text to Columns
Click Next twice
In step 3 of the wizard, click the Advanced button
Make sure the Trailing minus for negative numbers checkbox is checked
OK>Finish
 
T

T. Valko

2 step process...

Select the range of cells in question
Goto the menu Edit>Replace
Find what: CR
Replace with: -
Replace all

With the range still selected
Goto the menu Data>Text to Columns
Click Next twice
In step 3 of the wizard, click the Advanced button
Make sure the Trailing minus for negative numbers checkbox is checked
OK>Finish
 
A

ahwelch

That worked! You are a god!

Thank you both gentlemen for responding so quickly! Now, I may get to bed
earlier than 4am CST :)
 
A

ahwelch

That worked! You are a god!

Thank you both gentlemen for responding so quickly! Now, I may get to bed
earlier than 4am CST :)
 
J

Jacob Skaria

Using the Wizard is the best option.

If you need to use formula
=IF(RIGHT(A1,2)="CR",-SUBSTITUTE(SUBSTITUTE(A1,"CR",""),",",""),A1)

If this post helps click Yes
 
J

Jacob Skaria

Using the Wizard is the best option.

If you need to use formula
=IF(RIGHT(A1,2)="CR",-SUBSTITUTE(SUBSTITUTE(A1,"CR",""),",",""),A1)

If this post helps click Yes
 
Top