How To Combine Text And Numbers In Google Sheets

This tutorial will show you how to combine text and numbers in Google Sheets, and keep the the correct number formatting in place.

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

Total: $5,250
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
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.

Combine Text And Numbers 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.

For a deeper look at number formatting in Google Sheets, read my Google Sheets custom number format tutorial.

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.

10 thoughts on “How To Combine Text And Numbers In Google Sheets”

  1. Ben, my dates all turn out like this: 12/30/1899

    Here is my formula:
    =ArrayFormula(if(Z2:Z””,”StudentId: “&Z$2:Z
    &char(10)&”Role: “&AD$2:AD
    &char(10)&”Address: “& AE$2:AE
    &char(10)&”Entering Year: “& AJ$2:AJ
    &char(10)&”Entering Grade: “& AK$2:AK
    &char(10)&”Checklist Item: “& AM$2:AM
    &char(10)&”Date Requested: “&text(AN$2:AN,”mm/dd/yyyy”)
    &char(10)&”Date Due: “&text(AO$2:AO,”mm/dd/yyyy”)
    &char(10)&”Date Completed: “&text(AP$2:AP,”mm/dd/yyyy”)
    &char(10)&”Date Waived: “&text(AQ$2:AQ,”mm/dd/yyyy”)
    &char(10)&”School Program: “&AR$2:AR
    &char(10)&”Checklist Name: “&AS$2:AS
    &char(10)&”Par 1st Nm: “&AT$2:AT
    &char(10)&”Par Lst Nm: “&AU$2:AU
    &char(10)&”Par Email: “&AV$2:AV))

    Can you see my mistake?

    Many thanks (loving your courses, by the way).

    Brian

  2. I need this for mixed where second column needs to reference number only such as :
    A | B |
    1 Amp| =A:A*2 |
    Returns:
    1 Amp|#ERROR
    Your solution applied: 1 =TEXT(“Amp”)
    Returns:
    #ERROR|#ERROR
    (Error: Formula Parse Error)
    I’m confused. What do I need to do?

  3. Oh, I see. My example needed to be changed. It still does not resolve the second column.
    1&”Amp” Returns:
    1 Amp | #ERROR (Error: Function MULTIPLY parameter expects numbers. But “1 Amp” is a text and cannot be coerced to a number)

    1. You bet Shane, to separate the two use this as an example:

      If you want it to take the math you’ve already done.
      =text(B1,”$#.##”)&”, “&text(B2,”0.00%”)

      Or you could even get the calculations returned by replacing b1 or b2 in the example with the sum(a1*a2)

  4. Hi, I’m trying to put text in front of a squence but the methods I have tried seems to block the squence function. I have tried this:

    ="WEEK "&SEQUENCE(10,1,1,1)

    But it just outputs: ‘WEEK 1’ in one cell rather than all 10 rows.

    Can you help Please.

    1. Hi Shivaraj,

      Turn it into an array formula and it’ll output all 10 rows for you:

      =ArrayFormula("WEEK "&SEQUENCE(10,1,1,1))

      Cheers,
      Ben

  5. Thank you Ben!

    I’ve figured out how to use this except for inputting time into text. Is there a way to correctly format time in hours and minutes (00:00)?

    Every time I reference a time with text, it turns it into a decimal value of 24 hours. Example; 6:00 = 0.25. 12:00 = 0.5. and 24:00 = 1. Even when combining this with the text function (=text(B2,”0:00″), it does not display the correct time, but instead a tiny fraction of the correct time.

    Any help is greatly appreciated!

  6. Hello again.

    I just figured out my question using the formula below. I could not have done that without your website. Thank you so much!

    =text(B2,”H:MM”)

Leave a Reply

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