The purpose of the CONCATENATE function is really quite simple: it\u2019s used to combine multiple text strings together, resulting in one piece of text. In this article, I demonstrate how you can use CONCATENATE to join text in your spreadsheet in whatever format you\u2019re looking for.<\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Syntax<\/h3>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=CONCATENATE(text 1, [text 2], \u2026)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
- \n
- Where text 1<\/b>, text 2<\/b>, etc. are individual pieces of text that you want to join together. These can be references to a cell containing text, a number, or text that you type directly into the equation between a set of quotation marks.<\/li>\n<\/ul>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
How to use the CONCATENATE function<\/h3>\n
To demonstrate how to use the CONCATENATE function, take a look at the example below. I want to combine first and last names into one text string.<\/p>\n
I do this in the screenshot below with the formula =CONCATENATE(B2,” “,A2)<\/b>.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/1.-Concatenate-name-example.png” alt=”concatenate excel 1″ title_text=”1. Concatenate name example” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”443px” max_height=”443px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Notice that I\u2019ve added a space between the names by using a space in quotation marks (\u201c \u201c). If I didn\u2019t do this, the first and last names would be combined together without a space inbetween.<\/p>\n
Including the space, the function combines three different text items here. List each text string and separate them with a comma to join them all into one.<\/p>\n
I\u2019ll elaborate on this in the next example. Here, in addition to their full name, I want to write out how old each person is. I do this by taking the data from the spreadsheet (first name, last name, and age) and typing out the rest of the sentence within the formula.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/2.-Concatenate-name-and-age-example.png” alt=”concatenate excel 2″ title_text=”2. Concatenate name and age example” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”443px” max_height=”443px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Notice how I include the spaces within the text that I type in the formula. For example, before and after the \u201c is \u201c I have included a space, so that when it combines with the text before and after, the space is included.<\/p>\n
Alternatively, you can add the spaces separately (use \u201c \u201c separated by a comma within the formula). However, it\u2019s easier to include them with the other text in order to reduce the total number of items you\u2019re combining. And if you forget a space somewhere (which happens all the time!) you\u2019ll notice the mistake as soon as you finish the formula, and can easily go back and add it in.<\/p>\n
CONCATENATE with numbers<\/h4>\n
Now that you\u2019ve seen how to combine text in a few different ways, I\u2019ll show an example that uses numbers.<\/p>\n
When you include a number in the CONCATENATE function, you don\u2019t need to put it in quotation marks \u2013 just separate it with a comma like you would any other item within the function. You can even perform a calculation within the function. That is exactly what I do in this example, by multiplying each person\u2019s age by 12 to get their approximate age in months.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/3.-Concatenate-age-in-months-example.png” alt=”concatenate excel 3″ title_text=”3. Concatenate age in months example” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”443px” max_height=”443px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Notice how I didn\u2019t need to add a new column in the data to perform the calculation to turn years into months \u2013 I do it right in the formula by typing =CONCATENATE(B9,” “,A9,”‘s age in months is “, C9*12)<\/b>.<\/p>\n
Alternative to the CONCATENATE function: Ampersand (&)<\/h4>\n
Use the ampersand (&) instead of the CONCATENATE function for quick combinations of text. By putting an ampersand between the text you want to join, you instruct Excel to combine text without having to type out the CONCATENATE function.<\/p>\n
I do this in the example below with this formula:\u00a0=B2 & ” ” & A2<\/b>.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/4.-Concatenate-example.png” alt=”concatenate excel 4″ title_text=”4. Concatenate & example” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”443px” max_height=”443px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
The choice between using the Ampersand method and the CONCATENATE function really comes down to personal preference: they both work well for combining text, so pick whichever one you prefer!<\/p>\n
CONCATENATE with TEXT funtion<\/h4>\n
Combine the TEXT function with the CONCATENATE function to format numbers in your text.<\/p>\n
Take a look at the example below. I include the TEXT function in the TODAY function to indicate the day of each person\u2019s birthday (just assume they all share the same birthday for this example!).<\/p>\n
The formula I use here is =CONCATENATE(B2,” “,A2,”‘s birthday is “,TEXT(TODAY(),”DDDD”))<\/b>.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/5.-Concatenate-birthday-example.png” alt=”concatenate excel 5″ title_text=”5. Concatenate birthday example” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”443px” max_height=”443px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
That’s how to use CONCATENATE in Excel<\/h3>\n
With the CONCATENATE Excel function, you can join all kinds of text. This includes cell references, text typed into formulas, and numbers.<\/p>\n
On top of that, you can even perform calculations and format numbers within the formula. Who said Excel was just for numbers?!<\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text admin_label=”Connections T – Automate between spreadsheets – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button-light vertical-banner-container” _builder_version=”4.27.2″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/run-automatically-connect-sheet-icons.webp\")
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\")
<\/p>\n