Convert currency to general

A

Adam

Hi,

I was wondering is there any way to convert currency to a general format (or
text).
I need to export information from my AP application to my bank. However,
the query comes out of the AP application as a currency field, and the bank
wants it as text.

1e.
$1,480.67 needs to have the $ , and . stripped out (while retaining the
decimal places) so the bank receives it as 148067

Any help is appreciated.
 
P

Peo Sjoblom

How about

=TEXT(A1,"@")


in a help column, then copy and paste special over the old values, delete
the help column when everything has been copied over as values and send to
the bank.

Btw, always a good idea to make a backup copy of the raw data before doing
anything like this.

--


Regards,


Peo Sjoblom
 
G

Gord Dibben

Format to general to remove the currency sign.

Second part I don't understand........remove the , and . but retain the
decimal places.

If you delete the . there are no decimal places.

Try this...........................

Enter 100 in an empty cell.

Copy that cell.

Select the range to change and Paste Special>Multiply>OK>Esc.

Returns 148067


Gord Dibben MS Excel MVP
 
A

Adam

Hey, thanks for the help, that did convert it to the format i needed,
however, I need to exprt this now to a fixed length file, and I seem to lose
the function when i do this.
I'll muck around with it for a bit.

Thanks again!
 
J

Jacob Skaria

--Select the cells and format to text..
--that should take away the $ sign
--find/replace the others

--Find what: .
--Replace with (leave blank)
--Hit replace all

If this post helps click Yes
 
A

Adam

When I do this, it tells me it cannot find "."



Jacob Skaria said:
--Select the cells and format to text..
--that should take away the $ sign
--find/replace the others

--Find what: .
--Replace with (leave blank)
--Hit replace all

If this post helps click Yes
 
A

Adam

Actually, I got it to find the decimal, however, when it replaces it, it
leaves the space where the decimal was.

ie.

125.98
becomes

125 98
 
J

Jacob Skaria

You would have replaced that with a blank space. Leave the replace with field
with nothing in that and Hit replace All


If this post helps click Yes
 
A

Adam

The bank wants the amount in a fixed length field.

so:
1 = 1Cent
11 = 11 cents
111 = one dollar eleven cents
1111 = eleven dollars eleven cents

So, if I start with a whole alunt like $250.00
and I strip off the decimals leaving 250
The bank will now interpret that as two dollars and fifty cents,
 
A

Adam

I can't use a help column, because I need to export this as a flat (text
only), fixed length file.
 
A

Adam

Weird, I had this working the other night, now when I convert it to text, it
is dropping the decimals.
 
G

Gord Dibben

Then I guess you will have use the multiply by 100 trick before formatting
to no decimals.


Gord
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top