Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts

    add text before formula returning currency format

    Formula is =Max(J:J) and it returns a currency format " $2,000" [without the quotes]
    I want to add text [GGH] before the return and keep the currency format and see this in the cell - "GGH $2,000" [without the quotes]
    But I want Excel to see the numerical value of $2,000 for that cell so it can be used in other formulas.

    I also need to do the same with this formula, =(LOOKUP(9E+307,B:B))-MAX(B:B), and others.
    Would I have to alter the method?

    Thanks.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    SkiPro,

    What you need is a Custom Format and then just apply that to any cell you wish to have the prefix.
    SkiproCustomFMt.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    skipro (2014-12-02)

  4. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FWIW, you can also use the TEXT function:
    ="GGH "&TEXT(Max(J:J),"$ #,##0")
    for instance. RG's has the benefit of leaving the result as a number if you need it for other calculations, whereas this allows you to use another cell or formula for the text to concatenate (and the format if you wish).
    Regards,
    Rory

    Microsoft MVP - Excel

  5. The Following User Says Thank You to rory For This Useful Post:

    skipro (2014-12-02)

  6. #4
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    RG & Rory,
    Thanks.

Posting Permissions

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