Replacing accounting currency values with values that Excel recognises

[fh786]

Hi,

When I copy and paste values out of our finance package I get this:

2143.93 Cr
752.00 Dr

Basically I want Excel to convert them to something it recognises so I can AutoSum them, maybe have another column next to it with the converted figures.

So it will look something like this:

2143.93 Cr to -2143.93
752.00 Dr to 752.00

 

[SkipVought]

Hi,

Use Data > Text to columns to parse this into two columns. Then use an IF function to determine the sign...
[tt]
=if(b1="CR",-1,1)*a1
[/tt]
and copy down.

You ought to find an export feature in your financial appliaction. Chances are, that there's an export to Excel option that may save you this step.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fh786]

Thanks for the reply I tried that but it just comes up with: #VALUE! in the column?

 

[xlbo]

Did you do the "Text to columns"?

You would get a #VALUE error if the values it is trying to sum contain text or are actually text...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question