+ Reply to Thread
Results 1 to 4 of 4

Difficulty getting monetry value in 2DP within a text string

  1. #1
    Registered User
    Join Date
    10-19-2012
    Location
    York
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    59

    Difficulty getting monetry value in 2DP within a text string

    Hi

    I'm using several formalas to build a uinit pricing value, which calculates the price per "£" if greater than £1, or in pence if lower, ie.

    43.8p/100g and £1.53/lt

    The formula i'm using is below,

    =IF(BQ13<1,ROUNDDOWN(BQ13*100,1) & "p/" & $BI13, "£"&ROUNDDOWN(BQ13/100,2) &"/"&$BI13)

    The difficulty i'm facing is that if its £1.70 the formula calculates it as £1.7/L,
    any ideas on how i can get this back to £1.70/L

    I've tried a left concatanate but can't seem to get it working

    any help great appreciated!

    Adi

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,428

    Re: Difficulty getting monetry value in 2DP within a text string

    You'd need to use TEXT. TEXT(number, "£0.00") or something similar.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    10-19-2012
    Location
    York
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    59

    Re: Difficulty getting monetry value in 2DP within a text string

    Thanks TMShucks, added it in as below and it outputs correctly

    cheers

    =IF(BQ5<1,ROUNDDOWN(BQ5*100,1)&"p/"&$BI5,"£"&TEXT(ROUNDDOWN(BQ5/100,2),"0.00")&"/"&$BI5)

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,428

    Re: Difficulty getting monetry value in 2DP within a text string

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Find and extract text string from within another text string
    By huy_le in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-12-2013, 03:22 PM
  2. [SOLVED] Find partial text string within another text string return original text into cell.
    By mikey42979 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-17-2013, 02:58 PM
  3. copy only fonts(text color) from one string of text to another string of text
    By JMJ123 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-06-2013, 04:22 AM
  4. [SOLVED] Highlight a portion of string text (the string text is a value of a formula)
    By RaymondH in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-02-2012, 06:50 AM
  5. difficulty writing code for changing font color if cell contains specific text
    By curbster in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 08-13-2009, 03:23 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1