+ Reply to Thread
Results 1 to 11 of 11

text formula with formatting

  1. #1
    Registered User
    Join Date
    09-18-2017
    Location
    San Francisco
    MS-Off Ver
    2013
    Posts
    39

    text formula with formatting

    Hello,
    I have a column I formatted to have K and M for thousands and millions. So 1,000 would be 1k.
    I used this formula in the format cells-custom:
    [>=1000000] $# ## 0.0,,"M";[>=1000] $ # ## 0.0,"K"; # ## 0.0

    Now I need to add that to a text in the next column, but it doesn't keep the formatted cells (using & formula).
    So if I have a text "Deal size:" I want to add it to the 1k to get:
    Deal Size: 1k

    Right now I am getting "Deal Size 1000"

    How can i keep the formatted numbers when I add the text to it?

    Thank you!
    Last edited by alisachsf; 11-19-2019 at 05:36 PM.

  2. #2
    Forum Contributor
    Join Date
    11-05-2019
    Location
    Tiki Island, TX
    MS-Off Ver
    Office 365
    Posts
    168

    Re: text formula with formatting

    You have two choices. Just reference the cell +a1 and then include the text in the special formatting codes for that new cell. Like this:

    [>=1000000] "Deal Size: "$# ## 0.0,,"M";[>=1000] "Deal Size: "$ # ## 0.0,"K"; # ## 0.0

    or

    add a function to the worksheet like adding a macro using the code below and then reference the like this ="Deal Size: " & DisplayText("A1")
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-18-2017
    Location
    San Francisco
    MS-Off Ver
    2013
    Posts
    39

    Re: text formula with formatting

    Thank you!
    I used the macro and it almost works.
    currently i have a table where one of the columns has the formula and the macro, but every time I filter the table, the column with the formula is showing an #VALUE!
    If I double click on it, it will recalculate to the correct result, but i would like to have it do it automatically.
    What should I add?

    Thanks!!

  4. #4
    Forum Contributor
    Join Date
    11-05-2019
    Location
    Tiki Island, TX
    MS-Off Ver
    Office 365
    Posts
    168

    Re: text formula with formatting

    Check you calculation options. Make sure it is on fully automatic:
    Attachment 650391
    Attached Images Attached Images
    Last edited by DavidBowman; 11-19-2019 at 10:27 AM.

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: text formula with formatting

    David your attachment does not work.
    To attach see the yellow banner, the paper-clip is U/S.

  6. #6
    Forum Contributor
    Join Date
    11-05-2019
    Location
    Tiki Island, TX
    MS-Off Ver
    Office 365
    Posts
    168

    Re: text formula with formatting

    Thanks, have corrected.

  7. #7
    Registered User
    Join Date
    09-18-2017
    Location
    San Francisco
    MS-Off Ver
    2013
    Posts
    39

    Re: text formula with formatting

    Thanks, I do have it on automatic but it still doesn't recalculate when I switch the data source until i double click on it.
    Very strange.
    Any ideas what else could it be?

  8. #8
    Forum Contributor
    Join Date
    11-05-2019
    Location
    Tiki Island, TX
    MS-Off Ver
    Office 365
    Posts
    168

    Re: text formula with formatting

    Test clicking on data refresh all.
    Attached Images Attached Images

  9. #9
    Registered User
    Join Date
    09-18-2017
    Location
    San Francisco
    MS-Off Ver
    2013
    Posts
    39

    Re: text formula with formatting

    Refresh all works

  10. #10
    Forum Contributor
    Join Date
    11-05-2019
    Location
    Tiki Island, TX
    MS-Off Ver
    Office 365
    Posts
    168

    Re: text formula with formatting

    That's great. If you have a macro that updates the data source you can add
    Please Login or Register  to view this content.
    to the end of that macro and it will update automatically.

  11. #11
    Registered User
    Join Date
    09-18-2017
    Location
    San Francisco
    MS-Off Ver
    2013
    Posts
    39

    Re: text formula with formatting

    That worked!
    Thank you for all the help!

+ 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. Replies: 0
    Last Post: 09-12-2018, 10:19 AM
  2. [SOLVED] Formatting cells with Text formula
    By csnyder10 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-18-2017, 01:23 PM
  3. [SOLVED] Formatting a formula as text
    By heygravity in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-13-2015, 05:00 AM
  4. Formatting Cells with Date or Text Values in a Conditional Formatting Formula
    By Phil Hageman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-15-2014, 09:36 AM
  5. text formula + formatting
    By jw01 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-20-2014, 07:40 AM
  6. Formula bar and text formatting
    By jgallan in forum Excel General
    Replies: 1
    Last Post: 10-20-2013, 07:17 PM
  7. Formatting text to formula
    By Kajima01 in forum Excel General
    Replies: 1
    Last Post: 11-13-2009, 10:00 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