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

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!

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

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
---------------
Jacob Skaria


"ahwelch" wrote:

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!

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

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?


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"ahwelch" wrote:

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!

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

2 step process...

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

With the range still selected
Goto the menu DataText 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
OKFinish

--
Biff
Microsoft Excel MVP


"ahwelch" wrote in message
...
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!

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

=VALUE(0+SUBSTITUTE(TRIM(A20),"CR",""))

If this post helps click Yes
---------------
Jacob Skaria


"ahwelch" wrote:

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?


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"ahwelch" wrote:

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!

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

Oops

=VALUE(-SUBSTITUTE(TRIM(A21),"CR",""))

If this post helps click Yes
---------------
Jacob Skaria


"ahwelch" wrote:

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?


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"ahwelch" wrote:

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!

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

That worked! You are a god!

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

"T. Valko" wrote:

2 step process...

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

With the range still selected
Goto the menu DataText 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
OKFinish

--
Biff
Microsoft Excel MVP


"ahwelch" wrote in message
...
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!

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

Misunderstood and messed it not once but thrice...Sorry both...
--
If this post helps click Yes
---------------
Jacob Skaria


"ahwelch" wrote:

That worked! You are a god!

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

"T. Valko" wrote:

2 step process...

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

With the range still selected
Goto the menu DataText 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
OKFinish

--
Biff
Microsoft Excel MVP


"ahwelch" wrote in message
...
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!

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

Hi,

You may use this formula in a spare column

IF(ISERROR(SEARCH("CR",C4)),C4,-1*MID(C4,1,SEARCH("CR",C4,1)-1))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"ahwelch" wrote in message
...
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!

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

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Misunderstood and messed it not once but thrice...Sorry both...
--
If this post helps click Yes
---------------
Jacob Skaria


"ahwelch" wrote:

That worked! You are a god!

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

"T. Valko" wrote:

2 step process...

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

With the range still selected
Goto the menu DataText 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
OKFinish

--
Biff
Microsoft Excel MVP


"ahwelch" wrote in message
...
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!

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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"ahwelch" wrote in message
...
That worked! You are a god!

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

"T. Valko" wrote:

2 step process...

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

With the range still selected
Goto the menu DataText 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
OKFinish

--
Biff
Microsoft Excel MVP


"ahwelch" wrote in message
...
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!