Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Replacing accounting currency values with values that Excel recognises

Status
Not open for further replies.

fh786

Technical User
Jul 8, 2008
32
GB
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
 






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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Thanks for the reply I tried that but it just comes up with: #VALUE! in the column?
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top