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.
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.
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.
I do not want to concatenate a string or show the value like "Some Text " & C12. I need to use the text in a cell as part of the formula in another cell. For Example:
I have a cell containing a date 17.02.2014. I need to use that date in a formula within another cell:
='17.02.2014'!$AV$6However this date needs to be pulled dynamically, for this example from E26. I would have expected it to look something like:
='&E26&'!$AV$6What is the correct syntax?
asked Feb 17, 2014 at 14:54
Please try:
=indirect(E26&"!"&$AV$6)Indirect is able to read a string as a cell reference. Within the parentheses three elements are concatenated: the content of E26 (which could be as here a sheet named 17.02.2014), the required "!" and the content of AV6 (anchored with $s so it does not change if the formula is copied around).
answered Feb 17, 2014 at 15:17
pnutspnuts
16.9k5 gold badges50 silver badges102 bronze badges
1
I just landed here with this issue and the solution was still giving errors. Example:
A B C 1| String | INDIRECT() | Result -+---------------+------------------------+------------- 2| fred | =INDIRECT(A2)&"rick" | #REF 3| fred | =INDIRECT("A2")&"rick" | fredrickSo the instructions "must be and actual string" meant for me that the string is the string "A2" that represents the cell.
I hope it helps the next bloke or darling that comes upon this need.
Unfortunate thing about this is it don't scrape down, auto magically update the cells. So it's a pest.
Update: This works with dragging down on many cells
A B C 1| String | INDIRECT() | Result -+---------+--------------------------+------------- 2|non | =INDIRECT("A2:A")&"stop" |nonstop 3|short | =INDIRECT("A2:A")&"stop" |shortstop 4|pit | =INDIRECT("A2:A")&"stop" |pitstop 5|door | =INDIRECT("A2:A")&"stop" |doorstop 6|back | =INDIRECT("A2:A")&"stop" |backstopanswered Feb 6, 2017 at 18:19
1
The INDIRECT function will only accept a range, as explained in the Drive Help:
The thing with functions like NOW() and TODAY() is, that they do just that !!
The way to go is by referencing indirectly, like shown in the screenshot (C5 vs. C2).
Screenshot
Reference
//support.google.com/drive/answer/3093377
answered Feb 28, 2014 at 6:44
1
Sorry so late. But I got this to work for me in a similar matter:
=INDIRECT(INDIRECT("B13")&"!C2")"translating" to your needs, I think
=INDIRECT(INDIRECT("E26")&"!$AV$6")would do the trick!
serenesat
9,70930 gold badges30 silver badges48 bronze badges
answered Dec 17, 2020 at 22:23
2
For some reason, none of the solutions worked for me to consolidate Google Add-on Timesheet data from multiple sheets onto one sheet Then I tried this...
A B C 1| name of sheet | convert date to text | value from sheet -+---------------+-----------------------+------------------ 2| 03-15-21 | =TO_TEXT($A2) | =INDIRECT($B2&"!$B$7") 3| 03-22-21 | =TO_TEXT($A3) | =INDIRECT($B3&"!$B$7")Had to convert the date to text first. (Those using the Timesheet add-on, note that the value in column C points to the same cell on every sheet; the first cell containing combined time data is B7.)
Column C's formula gives null instead of !REF if the sheet is not yet renamed as the date or the sheet does not yet exist.
answered Mar 28, 2021 at 20:19
Code example for single cell (B1 containing a number):
="Quick numbers from "&indirect("B1")&" projects"
pnuts
16.9k5 gold badges50 silver badges102 bronze badges
answered Dec 7, 2017 at 10:05
0