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.

**Data Analysis in Google Sheets course**, where you’ll learn techniques and best practices for making decisions with your data. Check out the syllabus here >>

# 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:

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

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!

Hey Tom,

You could write:

`=TEXT( A1 , "$0,###" ) & " in sales"`

Hope that helps!

Ben

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?

=”TDS ” & TEXT( A1 , “0.00%” )

for the grams case you should change cell format to:

0″g”

or

0.00″g”

depending if you want decimal places or not.

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?

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

`=substitute( D36 , "k" , "") * 1000`

Thankyou for this!

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?

Hey James,

No, unfortunately the formatting (color, bold, italic, etc.) is applied to the whole cell.

Cheers,

Ben

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?

THANKS! Rockstar

Ok, am I going crazy over this.

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

For test i use

=text(25.5,”0.00″)

and even that aint working.

What am I doing wrong?

Thank you in advance.

Thanks, I noticed that you can combine it with formulas too, like:

=(“Total “)&sum(B3:B6)&(” hours”)

I would like to use the following formatting

[<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B"

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

Many thanks

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

So:=CONCATENATE(F3,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!

This is what the INDIRECT() function is for – look up the topic on dynamic named ranges for examples on how it is used.

Many thanks!

Hi.. Can I put 2 number with comma in a single cell in google sheet.

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: https://ibb.co/XWVzByk

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!

Thanks for the Post, it is very much helpful to me.

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.

In Excel 1995, 2003, 2007 I have spreadsheets with cells like this:

A1=431+”was the price”-1+”is the new price”

Thefore:

A1=430

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 !!

Hilton

Thanks for this Ben, I’m learning a lot about Google Sheets from you and greatly appreciate your posts.

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?