A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Convert currency to general



 
 
Thread Tools Display Modes
  #11  
Old November 13th, 2009, 08:32 PM posted to microsoft.public.excel.worksheet.functions
Adam
external usenet poster
 
Posts: 545
Default Convert currency to general

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.



.

  #12  
Old November 13th, 2009, 08:33 PM posted to microsoft.public.excel.worksheet.functions
Adam
external usenet poster
 
Posts: 545
Default Convert currency to general


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.

  #13  
Old November 13th, 2009, 11:35 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Convert currency to general

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.


.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.