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,"#,###")

Currency

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")

Percentages

Combining text and percentages:

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

Dates

Combining text and dates:

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

Can I see an example worksheet?

Yes, here you go.

4 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?

Leave a Reply

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