If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Convert currency to general
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. |
#2
|
|||
|
|||
Convert currency to general
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. |
#3
|
|||
|
|||
Convert currency to general
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. |
#4
|
|||
|
|||
Convert currency to general
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. |
#5
|
|||
|
|||
Convert currency to general
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. |
#6
|
|||
|
|||
Convert currency to general
--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. |
#7
|
|||
|
|||
Convert currency to general
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. |
#8
|
|||
|
|||
Convert currency to general
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. |
#9
|
|||
|
|||
Convert currency to general
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. |
#10
|
|||
|
|||
Convert currency to general
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. . |
|
Thread Tools | |
Display Modes | |
|
|