How can I combine text and numbers in a cell and keep the number formatting?

Combining text and numbers is common in spreadsheets, for example when you want to add some context:

e.g. Total: $5,250
e.g. Discount: 35.5%

However, if you try to combine text with numbers (or currencies/percentages/dates) directly in a cell in Google Sheets, you’ll notice the numbers loses any formatting, which will make the number harder to read or even less meaningful:

e.g. Total: 5250
e.g. Discount: 0.355

You can use the TEXT function to fix this, and apply formatting to numbers that are displayed with text in a cell.

How do you combine numbers and text in a cell in Google Sheets?

To combine text, in a cell or denoted by quotes "Text", and numbers, use the TEXT function, as shown in these examples.

For these examples, I’m assuming I have numbers, currency numbers, percentages or dates in column A:

Combining numbers and text in Google Sheets

What’s the formula?

With numbers in column A, then you can combine text and numbers with:

="Text and number: "&text(A1,"0.00")
="Text and number: "&text(A2,"#.##")

What’s the difference?

The 0 forces your cell to display zeros if a number has fewer digits than the format specifies, whereas the # does not force the display of zeros.

So TEXT(19.9,"000.00") displays 019.90, whereas TEXT(19.9,"###.##") displays 19.9.

Thousands separator

If your number is in the thousands, and you want to show the thousand separator comma, simply add that into the format section of the text formula. It works the same for the “0” or “#” notation:

="Text and thousands number: "&text(A3,"0,000")
="Text and thousands number: "&text(A3,"#,###")


Combining text and currency is easy enough, now you know how to format numbers. Simply add the currency symbol to the front of the number and it will show in your text:

="Currency: "&text(A4,"$0,000.00")


Combining text and percentages:

="Percentage: "&text(A5,"0.00%")


Combining text and dates:

="Date: "&text(A6,"mm/dd/yyyy")

Can I see an example worksheet?

Yes, here you go.

57 thoughts on “How can I combine text and numbers in a cell and keep the number formatting?”

  1. Ben, thank you for the content on this site – it’s been very helpful to me for a lot of reasons.

    I have a question about this formula – how do I modify it if I want the number first in the equation (the reverse of these examples)?

    So for example, if I wanted the readout to display:

    $100 in sales

    Sorry if this is an obvious question, can’t seem to figure it out!

  2. Pingback: Maggie Warneke
  3. Hi Ben,

    I am attempting to put a number in a cell, and I want the result to read “Text (number) [as a percentage of two decimal places]”

    eg: “TDS 1.60%”, so I want the “TDS and %” to be static. So its creating a rule for a column.

    And same again with a weight, I want to input a number and for it to spit it out with the “g” for grams automatically.

    I hope that explanation is clear and that makes sense?

  4. Hello, I’m wanting to use a number in a formula that I’m pulling from a public API, and the format is 14.2k for 14,200. Is there a way to translate that 14.2k to a number in Google Sheets?

    1. Yes! You can use the substitute function to remove the “k” and then multiply by 1,000:

      =substitute( D36 , "k" , "") * 1000
      1. Is there a way to do this in reverse? I have 32,000 but i want it to read 32K in my text string. Formatting is not changing the numbers in the text string

        1. Hey David,

          You could do this with either of these two formulae:




  5. Is there a way to format the number that is converted in the text function with respect to font, bold, color, etc. so that it is different than the text around it in the cell?

  6. Hi Ben,

    Can you share with me how to write a count formula if all the cells are combine with text and number in a individual cell ?

    3N in a cell
    M4 in another cell

    so how to write a formula to count from these cell?

  7. Ok, am I going crazy over this.

    No matter what I do the text function only gives me #error.

    For test i use
    and even that aint working.

    What am I doing wrong?

    Thank you in advance.

  8. I would like to use the following formatting


    Unfortunately, I am only getting errors. Is there a way to make this work?

    Many thanks

  9. Hello,
    I have a cell that the user types what column a specific cell sits in, and I want to combine the letters in that cell with a specific row to get the cell id for what I want to display. I can get the letters and number together but I cant make the answer be used to describe another cell.
    IE. choice of A, B, C, D columns.
    User chooses C and types it into cell F3
    I want to add the “row” number 7
    Which produces “C7”
    Now I want to know what value is in C7 : (=C7)
    But I cant make the contents of one field be used as the coordinate of another.
    I hope this makes sense…
    Thank you in advance!

  10. Hi all,

    Is there a way to have text in the same cell as a number used in a formula, and ignore the text?

    Ie: =125-sum(D2:Y2) when D2 is $14 Gas

    Image can be seen at:

    Right now I always have to use a different row to remind myself what each expense correlates to. If there is a way for the computer to only read the numbers so that I can write text in the same cell and it is ignored, I would save SO much space and be SOOOOO appreciative!

    1. I have searched & searched for cell format for the following with on resolve can anyone help me please?


      I can format the #’s
      ###-##-### but how do I get the a or b or whatever the letter is in the entry. It changes all the time so I want to just type 50912008a & have it format to the above. Again the a is not constant it changes.

  11. I am trying to format a google sheet cell with a date (##/##/##) followed by a comma, in order to be listed in a concatenate formula in a different cell.

    Weeks ago, I would enter 1/16/19, and it accepted it. Today, I enter that and it tells me I need a valid date.

    I have tried formatting it as plain text and also using the text function and neither seems to work.

  12. In Excel 1995, 2003, 2007 I have spreadsheets with cells like this:
    A1=431+”was the price”-1+”is the new price”
    No way to get the same in Office 365 (Excel 2016 + 5 licenses):
    A1= VALUE! (Error message)
    Any way to format such cell to avoid the error message?
    TIA !!

  13. Hi Ben! I wanted do copy the numbers ONLY on 1000 address and paste it on a new column. How do I do that without doing it one by one?

  14. Hi Ben

    Need your help!

    I want to combine text with a Currency Cell Value on another tab. But it doesn’t retain the currency format.

    Formula is

    =”A Incentive is being recommended to for incremental revenue generated during the period “&F12&” Baseline for this year’s incentive is “&’Sheet1′!F24

    Result is showing

    =”A Incentive is being recommended to for incremental revenue generated during the period 2019-20 Baseline for this year’s incentive is 141000

    I want the value to reflect £141,000 which is what is populated in Cell Sheet1′!F24

  15. This is why I hate using Google sheets and will throw a fit if I am ever forced to use it. As I am doing now. I just want a freakin “+” in front of my letter string (or words, but you know since you guys clearly don’t understand text I thought I’d break it down into something more manageable.)

    In other worse, I JUST WANT TO FORMAT A FREAKING CELL AS PLAIN TEXT. But no matter how many times I do it, it still converts the damn thing to a formula.

    This should not be a difficult task. Spreadsheets are often used for both numbers and text and the fact that I am hamstrung by this is ridiculous. The spreadsheet should be able to enter any combination of letters and numbers as text without resorting to inserting a damn formula to make it work.

    Why is that so hard for developers to understand? A spreadsheet should be multi-functional, not reduced to an overly complicated functions calculator.

    1. Hey Jennifer,

      I agree! Spreadsheets are frequently used for text and numbers and have been since their inception. They’re probably the most multi-functional software product ever built. A little knowledge goes a long way and it’s very easy to add a “+” to the start of your text, you just need to know how:

      First, a “+” or “=” at the first character in a cell means you want to enter a formula. That’s just the way you indicate that

      So, to show the “+” at the start of a text string, add a single quote at the very start, e.g.

      '+ your text

      which will show up as

      + your text

      in your cell. Hope that helps.


  16. Hi,
    When i enter a three digit number (eg . 123) in A1 cell of Sheet 1, i should get it in A1 cell of Sheet 2 as follows:
    SS/CS/123/19. Is it possible.
    Please help

    S. George

  17. Ben,

    This is all very helpful, thank you!

    In your formula =”Currency: “&text(A4,”$0,000.00”), is there a way for the comma placement to automatically adjust if the number is less than $1,000?

    The comma adjusts correctly for $1,000.00+ , however, anything less than $1,000.00 shows $0,999.00, etc.


  18. Hi Ben,

    I use: text(row(),”000000″)
    to add preceding zeros to the row numbers

    How to insert a “-” in the middle?
    for row number “000123”,
    I hope that it is displayed as “000-123”

    Thanks for any kinds of help!

  19. I am trying to format a sheet that has 3 columns. the first 2 will either have yes or no in them. the 3rd i would like to have the yes’s = $20….
    So if I have 2 Yes’s it would = $40…
    Is there anyway to do this????
    Mon Tue
    Jan 6 Jan 7
    Hadley yes yes
    Waylon no yes
    Total per day #VALUE! #VALUE!

  20. Hi,

    I currently have a Google Sheet with a column with currency that says eg. GBP 5,020.00

    I want to total up figures but it stops me due to the GBP text. How do I get rid of the text so i can just sum up the numbers? I have tried ‘pasting value only’ from the original data source but it still brings the GBP text across.

    Many Thanks

    1. You have few options, including:

      1) you could use the Data > Split Text to Columns feature to split into two columns
      2) highlight the column and use Find and Replace (press Ctrl + H) to replace all “GBP” (with nothing in the replace box)
      3) use a formula like =VALUE(RIGHT(A1,LEN(A1)-4)) or =VALUE(REGEXEXTRACT(A1,”[0-9,.]+”)) to remove the GDP

      Hope this helps!


  21. Hello, I’m trying to do this using a duration formatting. I tried doing the obvious things but they’re not working. I only need the hours:minutes displayed. The formula *NOT* working is:
    =concatenate(“Total Hours: “, “&duration(sum(C103:C198)*2, “##:##”)”)
    Can you help? Thanks!

  22. Perhaps a simpler question: how do I add text to a number field. For example an asterix * which would otherwise be a function. I want to keep the number for the calculation, but add an asterix just as a text.

    1. maybe try ‘*
      ‘ appears to be the escape to interpret the next symbol as literal as opposed to function?
      just passing thru with my own question but that might be it.

  23. Is there a way to make the cell show the following format?


    The “L” would be any letter. Also, have the spaces show in the formatting where the underscore is located.

  24. Is there a way I could enter a string and a number in a cell but when I apply a formula on the cell, it operates on the number.

    E.g.: In A1 : Wheat 2 kgs
    A2: Rice 1 kg
    A3: Rye 4 kgs

    A4: =sum(A1:A3) and it displays 7

  25. I’m trying to create a cell that contains a % calculation with the text “of forecast.”
    When I use CONCAT I get what I want but the calculation is still a decimal. I would like it to read as a %.
    I tried this:
    =CONCAT((TO_PERCENT(D18/F7)) " of forecast")
    Does not work – throws a #ERROR!

    =CONCAT(TEXT((D18/F7),"0.0%") " of forecast")
    Also does not work – throws a #ERROR!

    Is what I’m trying to do possible?
    It seems like it should . . . I can get the formula to work. But I just need the result of the calculation to be 58% instead of .5812244 etc.

    Any thoughts? Thank you!

    1. Hi Justice,

      You’re missing the commas in the CONCAT functions which is why you’re seeing the errors. This formula will work for you:

      =CONCAT(TEXT((D18/F7),"0.0%"), " of forecast")


  26. One of my cells is in currency (it is Cell E39). How can I make the information (the currency) hold its format when transferred to the next cell?
    This is my formula:
    = ( “Requisition” & ” ” & A39 &” : ” & B39 & “- “& C39 & ” – ” & D39 & ” ” & E39)
    F39 holds all the information input from A39 to E39. But when the information in E39 is transferred to F39, instead of seeing $210,000.00 I see 210000.

  27. Thank you so much for your help!
    I can’t seem to make my time code work though.
    Trying to combine A10 value “10:50” with B10 value “Pressure Gauges”.
    =text(A10,”##:##”)&” “&B10 gets me
    : Pressure Gauges
    =text(A10,”##:##”)&” “&B10 gets me
    00:00 Pressure Gauges
    Some more ideas please and thank you?

  28. Greetings,
    I want to have a column added with text and a dollar sign.

    I’m getting this:

    I’m using this:

    I want this:

  29. Hi Ben,
    I’m using a split formula to seperate a quantity from text. The format in which we’re receiving the data is
    “Text | quantity”
    I’m using the formula
    =arrayformula(trim(SPLIT(‘Input Sheet’!G3,”|”)))
    The issue is that the does not recognise the quantity as a number value and therefore i cannot calculate the sum.

    Have tried to apply this solution-
    =arrayformula(text(trim(SPLIT(‘Input Sheet’!G2,”|”)),”#”)
    but to no avail.

    Hope you can help!

  30. Hi Ben,
    I’m having some difficulties with getting rid of the decimal if the number happens to be is a whole number.

    So, if the number is 1432.0, I only want the number to display 1432, but currently, I get 1432. with the extra dot still showing up.

    As the numbers I’m working with sometimes have decimals and sometimes are whole numbers, I’d like to have a formula that applies for both situations.

Leave a Reply

Your email address will not be published. Required fields are marked *