In an earlier post, I have detailed the use of Google Sheets CONCATENATE function with the help of operators. But this Google Sheet post, which is related to the same topic, is slightly different from the way I have presented it.
Here I am giving some sample data that give you a clear picture on how and where to use adding text to text or text to date or text to number or all together in Google Sheets. So let’s begin.
Syntax:
CONCATENATE(string1, [string2, ...])
See how to use Concatenate function in Google Sheets.
Google Sheets CONCATENATE Function – Join Text, Date and Number
1. CONCATENATE Text and Text
How to Join Text and Text
To combine text and text in Google Sheets, just use the “&” between the texts or use the CONCATENATE function.
If you want to add a space between the text, just put the “ ”. See the example below to know where and how to use it.
In column D you can see the Concatenate formula.
A | B | C | D | |
1 | Text 1 | Tex 2 | Formula | Description |
2 | Mr. | Johnson | =A2&" "&B2 | You will get the result, Mr. Johnson. You can also use the CONCATENATE function. = CONCATENATE(A2, " ",B2) |
3 | Mr. | Arun | =A3&" "&B3 | You will get the result, Mr. Arun. Also is= CONCATENATE(A3, " ",B3) workable. |
4 | Ms. | Elina | =A4&B4 | You will get the result Ms.Elina. Also is= CONCATENATE(A4, B4) workable. |
2. CONCATENATE Text and Number or Numeric Value
Join Text and Number
It’s similar to the above example. Here also you can use the Concatenate function. But I am leaving that.
Just follow the example. It will give you an idea where you can use join text and number in a spreadsheet. You can either use the “&” or the Concatenate function itself.
A | B | C | D | |
1 | Product Code First Part | Product Code Second Part | Formula | Description |
2 | 25011 | AX | =A2&" "&B2 | 25011 AX |
3 | 25012 | AZ | =A3&" "&B3 | 25012 AZ. |
4 | Bill Value | Text | Formula | Description |
5 | 150.25 | Total Bill Amount is USD | =B5&" "&A5 | Total Bill Amount is USD 150.25 |
Similar Reading: Tips to Join Number & Text and Still Use in Calculation (Example, Rs. 100.00, USD 500.00)
3. CONCATENATE Date and Text
How to Join Date and Text
To add a date with a text we should use the TEXT function as follows.
Text(number,format)
So we can say it’s quite tough to understand compared to the above. But you can learn it quickly from the examples below.
You can see the Concatenate use in cell D2.
A | B | C | D | |
1 | Student Name | D.O.B. | Formula | Description |
2 | Emily | 23.11.2009 | A2&"'s"&" D.O.B. is "&text(B2,"dd/mm/yy") | Emily’s D.O.B. is 23.11.2009 =CONCATENATE(A2,"'s D.O.B. is ",text(B2,"dd/mm/yy")) |
3 | Liam | 26.03.2009 | =A3&"'s"&" D.O.B. is "&text(B3,"dd/mm/yy") | Liam’s D.O.B. is 26.03.2009 |
4 | Isabella | 31.03.2009 | =A4&"'s"&" D.O.B. is "&text(B4,"dd/mm/yy") | Isabella’s D.O.B. is 31.03.2009 |
5 | Text | Date | Formula | Description |
6 | Today is | =today() | =A6&" "&text(B6,"dd/mm/yyyy") | Today is 13/06/2017 |
4. CONCATENATE Date Time and Text
How to Join Date-Time/Timestamp and Text
See now how to concatenate date-time/timestamp with text.
When you combine time with text, you should know how to retain the AM/PM.
I am combining this date-time “20/07/2018 23:43:44” in the cell A1 with the text “recorded date and time is ”
="Recorded date-time is "&text(A1,"DD/MM/YYYY HH:MM:SS AM/PM")
The same with the help of Google Sheets Concatenate function.
=CONCATENATE("Recorded date-time is ",text(A1,"DD/MM/YYYY HH:MM:SS AM/PM"))
Hope you could understand the above Google Sheets tips to add text and date, text and text, add text and number and add text and date-time. It’s quite useful. So learn it and use it.