+ Reply to Thread
Results 1 to 6 of 6

Text function with custom number format

  1. 08-15-2016, 05:33 AM #1
    wally_j
    wally_j is offline
    Registered User
    Join Date
    07-01-2016
    Location
    London, UK
    MS-Off Ver
    2013
    Posts
    4

    Text function with custom number format

    Hi all,

    I am trying to create a custom data label column to help tidy up a chart I've put together to report on email subject line open rates. Screenshot below:

    oN2RuBr.jpg

    Column Z is the total number of delivered emails, and column AA is showing the number using a K for thousands or M for millions, using this custom number format:

    Please Login or Register  to view this content.
    How can I use this custom number format in column AC, where I want to combine values from other cells?

    I want the data label column to say: 33.0% O/R, 39K delivered, Week 30

    I am trying to use these functions together, but Excel is not happy about it! - 

    Please Login or Register  to view this content.
    Any ideas? Is it possible to use the text function with a custom number format?
    Reply With QuoteRegister To Reply
  2. 08-15-2016, 06:42 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,371

    Re: Text function with custom number format

    You need to nest an IF statement at that point in the concatenation.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 08-15-2016, 06:56 AM #3
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Text function with custom number format

    You need to contain your custom number format in quotation marks - which means putting the M and K in double quotation marks as well. Try this:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.
    Reply With QuoteRegister To Reply
  4. 08-15-2016, 07:56 AM #4
    wally_j
    wally_j is offline
    Registered User
    Join Date
    07-01-2016
    Location
    London, UK
    MS-Off Ver
    2013
    Posts
    4

    Re: Text function with custom number format

    Thank you so much for your help, the formula with the extra quotation marks worked perfectly.

    I really appreciate the response, cheers!
    Reply With QuoteRegister To Reply
  5. 08-15-2016, 07:58 AM #5
    xlnitwit
    xlnitwit is offline
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Text function with custom number format

    Hi,

    You can also escape characters using a \ symbol, which I find simpler for single characters. Thus:

    =TEXT(X14,"0.0%")&" O/R, "&TEXT(AA14,"[>=1000000]#.#0,,\M;[>=1000]#,\K;0")&" delivered, "&"Week "&AB14
    Don
    Please remember to mark your thread 'Solved' when appropriate.
    Reply With QuoteRegister To Reply
  6. 08-16-2016, 03:12 AM #6
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Text function with custom number format

    You're welcome - thanks for the feedback.

    If that solves your problem, please mark the thread as solved so others know that there's an answer here. See my sig for how to do it.

    Edit: and thanks for the rep.
    Last edited by Aardigspook; 08-16-2016 at 06:30 AM. Reason: Add thanks
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. TEXT function with custom format
    By sajmen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-30-2014, 11:54 AM
  2. Excel 2007 : Help to custom format number with text
    By scarames in forum Excel General
    Replies: 2
    Last Post: 03-25-2011, 09:31 AM
  3. Taking a custom number format to text?
    By jeanchile in forum Excel General
    Replies: 1
    Last Post: 03-04-2011, 09:06 PM
  4. Custom number format with text
    By karstens in forum Excel General
    Replies: 15
    Last Post: 01-18-2011, 12:29 PM
  5. custom number and random text format
    By CAD Teacher in forum Excel General
    Replies: 0
    Last Post: 06-21-2006, 07:00 AM
  6. Custom Number Format Text
    By Frank & Pam Hayes in forum Excel General
    Replies: 3
    Last Post: 12-03-2005, 01:40 PM
  7. change custom format number to text
    By joey in forum Excel General
    Replies: 3
    Last Post: 09-20-2005, 05:05 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1