Tags: assign, column, currency, excel, format, microsoft, money, msdn, receive, software, spreadsheet, states

Currency format

On Microsoft » Microsoft Excel

20,154 words with 8 Comments; publish: Fri, 23 May 2008 18:14:00 GMT; (306281.25, « »)

Hello,

Please help me.

In a spreadsheet I need to assign the currency that we receive money in.

There is a column that states the currency in a 3 letter code.

The amounts are downloaded from a CSV file.

Here is the fomula that I am using.

=IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$

#,##0.00_)"),IF($E51="EUR",TEXT($G51,"â?¬ #,##0.00_)"),"")))

The problem with this formula is that it converts the amount to text.

How can I get this or is there another formula that will give me the answer

in the relevant currency, as a value with the relevant currency, without

having to set the format using the "Cell Format" function?

Thank you for your help.

Max

All Comments

Leave a comment...

  • 8 Comments
    • There are many ways using Text or Value functions but I prefer the easiest

      one as follows:

      Any simple arithmetic calculation on text will convert them to numbers so

      add a zero to the formula (+0) and it will turn to number.

      --

      Rasoul Khoshravan Azar

      Kobe University, Kobe, Japan

      "Max" wrote:

      > Hello,

      > Please help me.

      > In a spreadsheet I need to assign the currency that we receive money in.

      > There is a column that states the currency in a 3 letter code.

      > The amounts are downloaded from a CSV file.

      > Here is the fomula that I am using.

      > =IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$

      > #,##0.00_)"),IF($E51="EUR",TEXT($G51,"â?¬ #,##0.00_)"),"")))

      > The problem with this formula is that it converts the amount to text.

      > How can I get this or is there another formula that will give me the answer

      > in the relevant currency, as a value with the relevant currency, without

      > having to set the format using the "Cell Format" function?

      >

      > Thank you for your help.

      > Max

      #1; Fri, 23 May 2008 18:16:00 GMT
    • What about the currency format?

      "Khoshravan" wrote:

      > There are many ways using Text or Value functions but I prefer the easiest

      > one as follows:

      > Any simple arithmetic calculation on text will convert them to numbers so

      > add a zero to the formula (+0) and it will turn to number.

      > --

      > Rasoul Khoshravan Azar

      > Kobe University, Kobe, Japan

      >

      > "Max" wrote:

      > > Hello,

      > > Please help me.

      > > In a spreadsheet I need to assign the currency that we receive money in.

      > > There is a column that states the currency in a 3 letter code.

      > > The amounts are downloaded from a CSV file.

      > > Here is the fomula that I am using.

      > > =IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$

      > > #,##0.00_)"),IF($E51="EUR",TEXT($G51,"â?¬ #,##0.00_)"),"")))

      > >

      > > The problem with this formula is that it converts the amount to text.

      > > How can I get this or is there another formula that will give me the answer

      > > in the relevant currency, as a value with the relevant currency, without

      > > having to set the format using the "Cell Format" function?

      > >

      > >

      > > Thank you for your help.

      > >

      > > Max

      #2; Fri, 23 May 2008 18:17:00 GMT
    • Using your present formula, say in Column H, is displaying the correct

      format, according to the contents of Column E.

      You can add these "text" values, as long as you *don't* use Sum().

      For example:

      =H51+H52+H53+H54 ... etc.

      And, of course, to format *that* totaling cell, for example:

      =TEXT($H51+$H52+$H53+$H54+$H55,"$ #,##0.00_)")

      --

      HTH,

      RD

      ----

      Please keep all correspondence within the NewsGroup, so all may benefit !

      ----

      "Max" <Max.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:A6C52782-7664-4C86-9035-4AB4E9ED5A0D.excel.todaysummary.com.microsoft.com...

      > Hello,

      > Please help me.

      > In a spreadsheet I need to assign the currency that we receive money in.

      > There is a column that states the currency in a 3 letter code.

      > The amounts are downloaded from a CSV file.

      > Here is the fomula that I am using.

      > =IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$

      > #,##0.00_)"),IF($E51="EUR",TEXT($G51,"â?¬ #,##0.00_)"),"")))

      > The problem with this formula is that it converts the amount to text.

      > How can I get this or is there another formula that will give me the

      answer

      > in the relevant currency, as a value with the relevant currency, without

      > having to set the format using the "Cell Format" function?

      >

      > Thank you for your help.

      > Max

      #3; Fri, 23 May 2008 18:18:00 GMT
    • Checking back, I don't think that my suggestion will work for adding those

      "text" cells, unless the regional settings recognize the currency symbol.

      Checking with USD ($) works for me.

      The only way I could get the â?¬ to total, was to change my regionals.

      Sorry!

      --

      Regards,

      RD

      ----

      Please keep all correspondence within the NewsGroup, so all may benefit !

      ----

      "Ragdyer" <RagDyer.excel.todaysummary.com.cutoutmsn.com> wrote in message

      news:OSCg4bwxHHA.3328.excel.todaysummary.com.TK2MSFTNGP03.phx.gbl...

      > Using your present formula, say in Column H, is displaying the correct

      > format, according to the contents of Column E.

      > You can add these "text" values, as long as you *don't* use Sum().

      > For example:

      > =H51+H52+H53+H54 ... etc.

      > And, of course, to format *that* totaling cell, for example:

      > =TEXT($H51+$H52+$H53+$H54+$H55,"$ #,##0.00_)")

      > --

      > HTH,

      > RD

      > ----

      -

      > Please keep all correspondence within the NewsGroup, so all may benefit !

      > ----

      -

      > "Max" <Max.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:A6C52782-7664-4C86-9035-4AB4E9ED5A0D.excel.todaysummary.com.microsoft.com...

      > > Hello,

      > > Please help me.

      > > In a spreadsheet I need to assign the currency that we receive money in.

      > > There is a column that states the currency in a 3 letter code.

      > > The amounts are downloaded from a CSV file.

      > > Here is the fomula that I am using.

      > > =IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$

      > > #,##0.00_)"),IF($E51="EUR",TEXT($G51,"â?¬ #,##0.00_)"),"")))

      > >

      > > The problem with this formula is that it converts the amount to text.

      > > How can I get this or is there another formula that will give me the

      > answer

      > > in the relevant currency, as a value with the relevant currency, without

      > > having to set the format using the "Cell Format" function?

      > >

      > >

      > > Thank you for your help.

      > >

      > > Max

      >

      #4; Fri, 23 May 2008 18:19:00 GMT
    • Hello Ragdryer,

      Thank you for your effort.

      If you try the Function "Fixed" under the Text formula selection it will

      give you the Currency selection that your computer is set at.

      I guess its back to the drawing board for me.

      Thank you again.

      Max

      "Ragdyer" wrote:

      > Checking back, I don't think that my suggestion will work for adding those

      > "text" cells, unless the regional settings recognize the currency symbol.

      > Checking with USD ($) works for me.

      > The only way I could get the â?¬ to total, was to change my regionals.

      > Sorry!

      > --

      > Regards,

      > RD

      > ----

      > Please keep all correspondence within the NewsGroup, so all may benefit !

      > ----

      > "Ragdyer" <RagDyer.excel.todaysummary.com.cutoutmsn.com> wrote in message

      > news:OSCg4bwxHHA.3328.excel.todaysummary.com.TK2MSFTNGP03.phx.gbl...

      > > Using your present formula, say in Column H, is displaying the correct

      > > format, according to the contents of Column E.

      > >

      > > You can add these "text" values, as long as you *don't* use Sum().

      > >

      > > For example:

      > >

      > > =H51+H52+H53+H54 ... etc.

      > >

      > > And, of course, to format *that* totaling cell, for example:

      > >

      > > =TEXT($H51+$H52+$H53+$H54+$H55,"$ #,##0.00_)")

      > >

      > > --

      > > HTH,

      > >

      > > RD

      > >

      > > ----

      > -

      > > Please keep all correspondence within the NewsGroup, so all may benefit !

      > > ----

      > -

      > > "Max" <Max.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > > news:A6C52782-7664-4C86-9035-4AB4E9ED5A0D.excel.todaysummary.com.microsoft.com...

      > > > Hello,

      > > > Please help me.

      > > > In a spreadsheet I need to assign the currency that we receive money in.

      > > > There is a column that states the currency in a 3 letter code.

      > > > The amounts are downloaded from a CSV file.

      > > > Here is the fomula that I am using.

      > > > =IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$

      > > > #,##0.00_)"),IF($E51="EUR",TEXT($G51,"â?¬ #,##0.00_)"),"")))

      > > >

      > > > The problem with this formula is that it converts the amount to text.

      > > > How can I get this or is there another formula that will give me the

      > > answer

      > > > in the relevant currency, as a value with the relevant currency, without

      > > > having to set the format using the "Cell Format" function?

      > > >

      > > >

      > > > Thank you for your help.

      > > >

      > > > Max

      > >

      >

      #5; Fri, 23 May 2008 18:20:00 GMT
    • If you can use a macro solution it would be pretty easy, I think.

      _________________________________________________________________________

      "Max" <Max.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:F24A873C-98D9-418B-91B8-019AE759BA3D.excel.todaysummary.com.microsoft.com...

      > Hello Ragdryer,

      > Thank you for your effort.

      > If you try the Function "Fixed" under the Text formula selection it will

      > give you the Currency selection that your computer is set at.

      > I guess its back to the drawing board for me.

      > Thank you again.

      > Max

      > "Ragdyer" wrote:

      >> Checking back, I don't think that my suggestion will work for adding

      >> those

      >> "text" cells, unless the regional settings recognize the currency symbol.

      >> Checking with USD ($) works for me.

      >> The only way I could get the ? to total, was to change my regionals.

      >> Sorry!

      >> --

      >> Regards,

      >> RD

      >> ----

      >> Please keep all correspondence within the NewsGroup, so all may benefit !

      >> ----

      >> "Ragdyer" <RagDyer.excel.todaysummary.com.cutoutmsn.com> wrote in message

      >> news:OSCg4bwxHHA.3328.excel.todaysummary.com.TK2MSFTNGP03.phx.gbl...

      >> > Using your present formula, say in Column H, is displaying the correct

      >> > format, according to the contents of Column E.

      >> >

      >> > You can add these "text" values, as long as you *don't* use Sum().

      >> >

      >> > For example:

      >> >

      >> > =H51+H52+H53+H54 ... etc.

      >> >

      >> > And, of course, to format *that* totaling cell, for example:

      >> >

      >> > =TEXT($H51+$H52+$H53+$H54+$H55,"$ #,##0.00_)")

      >> >

      >> > --

      >> > HTH,

      >> >

      >> > RD

      >> >

      >> > ----

      >> -

      >> > Please keep all correspondence within the NewsGroup, so all may benefit

      >> > !

      >> > ----

      >> -

      >> > "Max" <Max.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      >> > news:A6C52782-7664-4C86-9035-4AB4E9ED5A0D.excel.todaysummary.com.microsoft.com...

      >> > > Hello,

      >> > > Please help me.

      >> > > In a spreadsheet I need to assign the currency that we receive money

      >> > > in.

      >> > > There is a column that states the currency in a 3 letter code.

      >> > > The amounts are downloaded from a CSV file.

      >> > > Here is the fomula that I am using.

      >> > > =IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$

      >> > > #,##0.00_)"),IF($E51="EUR",TEXT($G51,"? #,##0.00_)"),"")))

      >> > >

      >> > > The problem with this formula is that it converts the amount to text.

      >> > > How can I get this or is there another formula that will give me the

      >> > answer

      >> > > in the relevant currency, as a value with the relevant currency,

      >> > > without

      >> > > having to set the format using the "Cell Format" function?

      >> > >

      >> > >

      >> > > Thank you for your help.

      >> > >

      >> > > Max

      >> >

      >>

      #6; Fri, 23 May 2008 18:21:00 GMT
    • Hello Vasant,

      Thank you for you effort, I am sure using a macro will be the only way.

      Thank you and best regards

      Max

      "Vasant Nanavati" wrote:

      > If you can use a macro solution it would be pretty easy, I think.

      > _________________________________________________________________________

      > "Max" <Max.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:F24A873C-98D9-418B-91B8-019AE759BA3D.excel.todaysummary.com.microsoft.com...

      > > Hello Ragdryer,

      > > Thank you for your effort.

      > > If you try the Function "Fixed" under the Text formula selection it will

      > > give you the Currency selection that your computer is set at.

      > >

      > > I guess its back to the drawing board for me.

      > >

      > > Thank you again.

      > >

      > > Max

      > >

      > > "Ragdyer" wrote:

      > >

      > >> Checking back, I don't think that my suggestion will work for adding

      > >> those

      > >> "text" cells, unless the regional settings recognize the currency symbol.

      > >> Checking with USD ($) works for me.

      > >> The only way I could get the ? to total, was to change my regionals.

      > >>

      > >> Sorry!

      > >> --

      > >> Regards,

      > >>

      > >> RD

      > >>

      > >> ----

      > >> Please keep all correspondence within the NewsGroup, so all may benefit !

      > >> ----

      > >>

      > >> "Ragdyer" <RagDyer.excel.todaysummary.com.cutoutmsn.com> wrote in message

      > >> news:OSCg4bwxHHA.3328.excel.todaysummary.com.TK2MSFTNGP03.phx.gbl...

      > >> > Using your present formula, say in Column H, is displaying the correct

      > >> > format, according to the contents of Column E.

      > >> >

      > >> > You can add these "text" values, as long as you *don't* use Sum().

      > >> >

      > >> > For example:

      > >> >

      > >> > =H51+H52+H53+H54 ... etc.

      > >> >

      > >> > And, of course, to format *that* totaling cell, for example:

      > >> >

      > >> > =TEXT($H51+$H52+$H53+$H54+$H55,"$ #,##0.00_)")

      > >> >

      > >> > --

      > >> > HTH,

      > >> >

      > >> > RD

      > >> >

      > >> > ----

      > >> -

      > >> > Please keep all correspondence within the NewsGroup, so all may benefit

      > >> > !

      > >> > ----

      > >> -

      > >> > "Max" <Max.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > >> > news:A6C52782-7664-4C86-9035-4AB4E9ED5A0D.excel.todaysummary.com.microsoft.com...

      > >> > > Hello,

      > >> > > Please help me.

      > >> > > In a spreadsheet I need to assign the currency that we receive money

      > >> > > in.

      > >> > > There is a column that states the currency in a 3 letter code.

      > >> > > The amounts are downloaded from a CSV file.

      > >> > > Here is the fomula that I am using.

      > >> > > =IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$

      > >> > > #,##0.00_)"),IF($E51="EUR",TEXT($G51,"? #,##0.00_)"),"")))

      > >> > >

      > >> > > The problem with this formula is that it converts the amount to text.

      > >> > > How can I get this or is there another formula that will give me the

      > >> > answer

      > >> > > in the relevant currency, as a value with the relevant currency,

      > >> > > without

      > >> > > having to set the format using the "Cell Format" function?

      > >> > >

      > >> > >

      > >> > > Thank you for your help.

      > >> > >

      > >> > > Max

      > >> >

      > >>

      > >>

      >

      >

      #7; Fri, 23 May 2008 18:22:00 GMT
    • Hello Ragdryer,

      I made a mistake the function is not "Fixed"it is "Dollar".

      Thanks

      Max

      "Max" wrote:

      > Hello Ragdryer,

      > Thank you for your effort.

      > If you try the Function "Fixed" under the Text formula selection it will

      > give you the Currency selection that your computer is set at.

      > I guess its back to the drawing board for me.

      > Thank you again.

      > Max

      > "Ragdyer" wrote:

      > > Checking back, I don't think that my suggestion will work for adding those

      > > "text" cells, unless the regional settings recognize the currency symbol.

      > > Checking with USD ($) works for me.

      > > The only way I could get the â?¬ to total, was to change my regionals.

      > >

      > > Sorry!

      > > --

      > > Regards,

      > >

      > > RD

      > >

      > > ----

      > > Please keep all correspondence within the NewsGroup, so all may benefit !

      > > ----

      > >

      > > "Ragdyer" <RagDyer.excel.todaysummary.com.cutoutmsn.com> wrote in message

      > > news:OSCg4bwxHHA.3328.excel.todaysummary.com.TK2MSFTNGP03.phx.gbl...

      > > > Using your present formula, say in Column H, is displaying the correct

      > > > format, according to the contents of Column E.

      > > >

      > > > You can add these "text" values, as long as you *don't* use Sum().

      > > >

      > > > For example:

      > > >

      > > > =H51+H52+H53+H54 ... etc.

      > > >

      > > > And, of course, to format *that* totaling cell, for example:

      > > >

      > > > =TEXT($H51+$H52+$H53+$H54+$H55,"$ #,##0.00_)")

      > > >

      > > > --

      > > > HTH,

      > > >

      > > > RD

      > > >

      > > > ----

      > > -

      > > > Please keep all correspondence within the NewsGroup, so all may benefit !

      > > > ----

      > > -

      > > > "Max" <Max.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > > > news:A6C52782-7664-4C86-9035-4AB4E9ED5A0D.excel.todaysummary.com.microsoft.com...

      > > > > Hello,

      > > > > Please help me.

      > > > > In a spreadsheet I need to assign the currency that we receive money in.

      > > > > There is a column that states the currency in a 3 letter code.

      > > > > The amounts are downloaded from a CSV file.

      > > > > Here is the fomula that I am using.

      > > > > =IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$

      > > > > #,##0.00_)"),IF($E51="EUR",TEXT($G51,"â?¬ #,##0.00_)"),"")))

      > > > >

      > > > > The problem with this formula is that it converts the amount to text.

      > > > > How can I get this or is there another formula that will give me the

      > > > answer

      > > > > in the relevant currency, as a value with the relevant currency, without

      > > > > having to set the format using the "Cell Format" function?

      > > > >

      > > > >

      > > > > Thank you for your help.

      > > > >

      > > > > Max

      > > >

      > >

      > >

      #8; Fri, 23 May 2008 18:23:00 GMT