Comments on: Excel TEXT function with formula examples

Microsoft Excel is all about numbers, and in your worksheets, you are likely to deal with numbers most of the time. However, there are situations when you do need to convert numbers to text, and it is where the TEXT function steps in. Continue reading →

Comments page 2. Total comments: 71

  1. Hi, is there a formatting that can convert text to subscript or superscript?

  2. When I import a text field "1E1" into MS Excel, it converts to "1.00E+01". How can I stop it from converting OR what is a formula to reverse the scientific notation. Currently, when I try to convert it back it turns into "10".

    1. Kindly note for your problem "1E1"

      =text(A2,"@")

  3. Hi i am trying to create a string of numbers with initials at the end that is populated from a cell with an if statement.
    i need the number to read 2019-000-AA
    2019 comes from cell L12, 000 is from cell M12 (i need the leading Zero to show when the number is only a double digit or single digit - i need it to have the 0 placeholders) and AA is from J4.
    I also have a macro that needs to run adding plus 1 to the number in cell M12. i have the code for this, but it is not working and cell I4 when =L12&M12&J4 does not show the 0 place holder before the middle number (M12)

    1. You want to wrap the middle cell in a TEXT() function. The other portions seem to be taking care of themselves, but since the middle cell can be a single place, two, or three but needs to always be three here, it is a natural for the TEXT() function. For example:

      =L12& TEXT( M12, "000" ) &J4

      Then you will always have at least three places filled.

  4. I'm trying to concatenate a number with the " (double quote) sign.

    So I'd like to convert the number 90 to 90"

    I've tried =text(A1,"""), where the number is in A1, but no luck. Any ideas?

    1. Try putting the " outside the text conversion =text(A1)&" or even just =A1&"""

  5. In scientific report, we often show 0.183 as .183, and -0.179 as -.179. How can I remove the leading zeor? Please help.

    1. In addition to Doug's response (again, thank you Doug!):

      If your values have a variable number of digits after the decimal point, you may want to use the # placeholder that does not display extra zeros.

      For example, the .0000 format code will display 0.183 as .1830 and the .#### format code will display it as .183

      For more information, please see Custom Excel number format.

    2. Sti:
      I believe you can get the formatting you want by right clicking the cell that holds the data then Format Cells then Custom then enter .0000 in the Type field and OK.

  6. I am trying to get the formula ="+ "&SUM(I29-D29)/D29 to appear as +23% It appears as +0.231952922620801 Have tried clicking % function button, using formula pull down menu and format menu. Nothing works. Is it possible to place a text + in front of a calculated percentage in excel? Yes, I realize the plus is assumed but my boss wants it to appear in the spreadsheet.

    Using I29 number of 246,823
    D29 number as 200,351

    1. ROUND the number you're inserting into the text field and multiply by 100
      ie &ROUND(SUM(I29-D290)/D29,2)*100 will convert the 0.23195... into 0.23 * 100 = 23

  7. Hi

    Today is 1 Feb 2018 (01/02/2018 in dd/mm/yyyy).

    I want to retrieve the month portion of the current date.

    I can do that with MONTH(TODAY()), and it will return 2.

    So far so good.

    If I try TEXT(MONTH(TODAY()),"mm"), it returns "01".

    I have tried using a new sheet on my Citrix computer - it returns "01".

    I have tried using a new sheet on my BBB Android phone it returns "01".

    Any clues?

    Pete
    (BBB = Beloved BlackBerry)

    1. Hi Pete,
      You're getting that problem because you're converting the date to the number 2, and then you're telling it to convert the number 2 into a date, which it then knows as 02/01/1900 (dd/mm/yyyy), so then when you run the text formula, you're getting the 1 from the January.

  8. In the formula below, the negative difference does not appear in Red. Is there a different formatting method to make the negatives red?

    =TEXT(+H37-G37,"#,##0_);[RED](#,##0)")&" ( "&TEXT(IFERROR(H37/G37-1,0),"%0.0")&" )"

    1. Hello,

      Please note that the TEXT function does not support color formatting. So if you copy a number format code from the Format Cells dialog that includes a color, e. g. $#,##0.00_);[Red]($#,##0.00), the TEXT function will accept the format code, but it won’t display the color.

      Hope it will help you.

  9. =TEXT(A1;"FL-00;BS-00;GR±00;@")
    hello, iam trying to use the mentioned function to add prefix (FL-) before any positive number or (BS-) before any negative number or (GR±00) if the number is 0 or to use the same text in cell

    this function is working for positive numbers only and for zero it shows (R±00) without G .

    if you can help me, I will be appreciated

  10. My question is,, in this formula =if(A1>B1, "Yes", "No").
    I want Yes in bold, is it possible using Text function.

    NO VBA Code please.

    1. Hello Rajesh,

      The TEXT function can only change the way a number appears in a cell by applying a custom number format to it. It cannot apply bold, italic, or underlining style. To my best knowledge, none of the Excel functions can do that.

      1. You can apply Conditional Formatting instead. It will be easier.

  11. Hi,
    I would like to know how I can add text to the number format?
    Usually, if you edit the cell format, I would enter a custom format:

    dd "Days" hh "Hours"

    But if I try that in the TEXT function, it doesn't work because of the extra " characters:

    =text(today(), "dd "Days" hh "Hours"")

    Any idea of a work around?

    1. Hi Piet,
      Does using =TEXT(TODAY(),"dd ") & "Days " & TEXT(TODAY(),"hh ") & "Hours" give you what you require?

  12. i am concatenating headings and associated numbers for a report.
    all of the numbers have a currency fomrat.
    i am using the followning function to show thousand separators: "£#,##0;;@"
    the positiveappear fine but the negetive numbers do not appear

    1. Hi Luke,

      This is because the negative numbers section is empty in your format code.
      To have the format apply to all number types - positive, negative and zeros - use only the first section of the format code: "£#,##0"

      You can find more information in Understanding Excel number format.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)