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.

In a helper column:

=SUBSTITUTE(SUBSTITUTE(A2,"$",""),".","")

"Adam" wrote:

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.

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


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

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 SpecialMultiplyOKEsc.

Returns 148067


Gord Dibben MS Excel MVP

On Mon, 9 Nov 2009 13:24:01 -0800, Adam
wrote:

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.

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!


"Sean Timmons" wrote:

In a helper column:

=SUBSTITUTE(SUBSTITUTE(A2,"$",""),".","")

"Adam" wrote:

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.

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


"Adam" wrote:

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!


"Sean Timmons" wrote:

In a helper column:

=SUBSTITUTE(SUBSTITUTE(A2,"$",""),".","")

"Adam" wrote:

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.

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



"Jacob Skaria" wrote:

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


"Adam" wrote:

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!


"Sean Timmons" wrote:

In a helper column:

=SUBSTITUTE(SUBSTITUTE(A2,"$",""),".","")

"Adam" wrote:

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.

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



"Jacob Skaria" wrote:

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


"Adam" wrote:

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!


"Sean Timmons" wrote:

In a helper column:

=SUBSTITUTE(SUBSTITUTE(A2,"$",""),".","")

"Adam" wrote:

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.

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


"Adam" wrote:

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



"Jacob Skaria" wrote:

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


"Adam" wrote:

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!


"Sean Timmons" wrote:

In a helper column:

=SUBSTITUTE(SUBSTITUTE(A2,"$",""),".","")

"Adam" wrote:

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.

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,

"Gord Dibben" wrote:

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 SpecialMultiplyOKEsc.

Returns 148067


Gord Dibben MS Excel MVP

On Mon, 9 Nov 2009 13:24:01 -0800, Adam
wrote:

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.

.

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


"Peo Sjoblom" wrote:

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


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



.

Weird, I had this working the other night, now when I convert it to text, it
is dropping the decimals.
"Jacob Skaria" wrote:

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


"Adam" wrote:

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



"Jacob Skaria" wrote:

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


"Adam" wrote:

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!


"Sean Timmons" wrote:

In a helper column:

=SUBSTITUTE(SUBSTITUTE(A2,"$",""),".","")

"Adam" wrote:

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.

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


Gord

On Fri, 13 Nov 2009 11:28:03 -0800, Adam
wrote:

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,

"Gord Dibben" wrote:

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 SpecialMultiplyOKEsc.

Returns 148067


Gord Dibben MS Excel MVP

On Mon, 9 Nov 2009 13:24:01 -0800, Adam
wrote:

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.

.