+ Reply to Thread
Results 1 to 6 of 6

Text function with custom number format

  1. #1
    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?

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,958

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,342

    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've just moved house, internationally, during COVID (!) 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.

  4. #4
    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!

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,084

    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.

  6. #6
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,342

    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 to 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. [SOLVED] custom number and random text format
    By CAD Teacher in forum Excel General
    Replies: 0
    Last Post: 06-21-2006, 07:00 AM
  6. [SOLVED] 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1