Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Currency formatting dilemna

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    OKC
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I import data into a CSV file then convert it to excel xls. One column of data has a comma in the number like this 19,456 which actually that is supposed to be 194.56 (yes the mainframe is screwed up and sends the data with the comma in the wrong position.) Anyhow, I have to convert this to a currency figure so using the following:
    =left(b2,3)&"."&right(b2,2)
    I get 194.56 then I copy and paste values over it to get rid of the formula, but then i cannot get the $ formatting to work. How do I get the currency or accounting or dollar formatting to work on the resulting cell?

    Eddie G.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Eddie.
    Give this a try...
    =TEXT(LEFT(B2,3)&"."&RIGHT(B2,2),"$#,##0.00_);($#,##0.00)")

    Tom

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-16 07:27, Eddie G. wrote:
    I import data into a CSV file then convert it to excel xls. One column of data has a comma in the number like this 19,456 which actually that is supposed to be 194.56 (yes the mainframe is screwed up and sends the data with the comma in the wrong position.) Anyhow, I have to convert this to a currency figure so using the following:
    =left(b2,3)&"."&right(b2,2)
    I get 194.56 then I copy and paste values over it to get rid of the formula, but then i cannot get the $ formatting to work. How do I get the currency or accounting or dollar formatting to work on the resulting cell?

    Eddie G.
    After you paste your "new" values, select 'em, choose the Data | Text to Columns... menu command, and press [ Finish ].

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    OKC
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks to both responses....both ways work great.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,426
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    Make the conversion a VALUE by


    a) copy a blank cell
    select cell with converted text
    Edit Paste Special Add

    b) use Value
    =VALUE(LEFT(B2,3)&"."&RIGHT(B2,2))

    With either of the above, format as Currency


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •