{"id":43936,"date":"2024-05-02T18:00:11","date_gmt":"2024-05-02T16:00:11","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=43936"},"modified":"2024-05-29T21:42:11","modified_gmt":"2024-05-29T19:42:11","slug":"concatenate-cells-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/concatenate-cells-in-excel\/","title":{"rendered":"How to Concatenate Cells and Strings in Excel &#8211; Step by Step Guide"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/05\/Concatenate-cells-in-excel.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-44183 size-large\" title=\"Concatenate cells in excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/05\/Concatenate-cells-in-excel-1024x576.png\" alt=\"Concatenate cells in excel\" width=\"640\" height=\"360\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/05\/Concatenate-cells-in-excel-1024x576.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/05\/Concatenate-cells-in-excel-300x169.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/05\/Concatenate-cells-in-excel-768x432.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/05\/Concatenate-cells-in-excel-180x100.png 180w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/05\/Concatenate-cells-in-excel.png 1280w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/a><br \/>\nDelve into the realm of <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/free-microsoft-excel-online-course\/\" target=\"_blank\" rel=\"noopener\">Microsoft Excel<\/a>&#8216;s CONCATENATE function, where separate strings of text are seamlessly woven into a single coherent strand. This function, designed for merging text strings, maintains structure and enhances readability in Excel spreadsheets, reducing clutter effectively.<\/p>\n<p><strong>Key Takeaways:<\/strong><\/p>\n<ul>\n<li>Simplicity with CONCATENATE: Merge text strings effortlessly, maintaining spreadsheet structure and enhancing readability.<\/li>\n<li>Ampersand Magic: Utilize the &#8220;&amp;&#8221; symbol to combine text and cells swiftly, perfect for including fixed text within the combined result.<\/li>\n<li>Efficiency with CONCAT and TEXTJOIN: Concatenate cell ranges or customize delimiters swiftly using CONCAT and TEXTJOIN functions, saving time and manual entry fuss.<\/li>\n<li>Addressing Common Pitfalls: Overcome issues like table expansion errors and formatting challenges with line breaks or delimiters using advanced techniques.<\/li>\n<li>Creative Fusion with Functions: Combine CONCATENATE with other Excel functions for dynamic formulas, transforming and presenting data effectively.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_57_1 counter-flat ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li><a href=\"#\" class=\"scroll-to-download\">Free Practice Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/concatenate-cells-in-excel\/#Introduction_to_Concatenation_in_Excel\" title=\"Introduction to Concatenation in Excel\">Introduction to Concatenation in Excel<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/concatenate-cells-in-excel\/#Tips_on_Concatenating_Multiple_Ranges_and_Strings\" title=\"Tips on Concatenating Multiple Ranges and Strings\">Tips on Concatenating Multiple Ranges and Strings<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/concatenate-cells-in-excel\/#Common_Concatenation_Pitfalls_and_How_to_Avoid_Them\" title=\"Common Concatenation Pitfalls and How to Avoid Them\">Common Concatenation Pitfalls and How to Avoid Them<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/concatenate-cells-in-excel\/#Advanced_Techniques_for_Data_Combination_in_Excel\" title=\"Advanced Techniques for Data Combination in Excel\">Advanced Techniques for Data Combination in Excel<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/concatenate-cells-in-excel\/#FAQs\" title=\"FAQs\">FAQs<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Introduction_to_Concatenation_in_Excel\"><\/span>Introduction to Concatenation in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3>Understanding the CONCATENATE Function<\/h3>\n<p>Diving into the world of Excel, you might find yourself needing to weave separate strings of text into a single coherent strand. That&#8217;s where the <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/concatenate-in-excel\/\" target=\"_blank\" rel=\"noopener\">CONCATENATE function<\/a> steps in to save the day. It&#8217;s a text function designed specifically for this purpose, allowing you to merge two or more text strings seamlessly.<\/p>\n<p>The beauty of this function lies in its simplicity: with it, your Excel spreadsheets can maintain structure, enhance readability, and reduce clutter by consolidating information effectively.<\/p>\n<h3>Step-by-Step Guide on Concatenating Cells<\/h3>\n<p>Unleashing the full potential of the CONCATENATE function is like mastering a secret Excel spell. To get started, pick the cells you want to merge and type &#8220;=CONCATENATE(&#8221; followed by the cell references, separating each with a comma. Hit enter, and voila! The cells blend like a culinary masterpiece.<\/p>\n<p>Consider a list with first names in column A and last names in column B. To craft full names in column C, your formula might look like &#8220;=CONCATENATE(A2, &#8221; &#8220;, B2)&#8221;. Notice the space (&#8221; &#8220;) added as a text argument? It ensures that your final text won&#8217;t be a jumble of characters but rather a neatly spaced combination.<\/p>\n<p><strong>STEP 1: <\/strong>In cell C2, enter the formula &#8220;=CONCATENATE(A2, &#8221; &#8220;, B2)&#8221; to combine the first and last names with a space in between.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A153A07.580981_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-3.png\" alt=\"Concatenate Cells\" width=\"882\" height=\"397\" \/><\/p>\n<p><strong>STEP 2: <\/strong>Drag the formula down to apply it to all rows in the dataset.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A153A17.859561_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-4.png\" alt=\"Concatenate Cells\" width=\"801\" height=\"415\" \/><\/p>\n<p>Enjoy the magic of CONCATENATE for seamless text merging in Excel!<\/p>\n<h3>Combining Text and Cells with &#8220;&amp;<\/h3>\n<p>When it comes to Excel wizardry, combining text and cells using the ampersand (&#8220;&amp;&#8221;) is like finding a shortcut on a long road. Start with &#8220;=&#8221;, click on the cell you wish to combine, type &#8220;&amp;&#8221;, and then type or select the next part\u2014repeat as needed. This approach is especially handy when you want to include fixed text\u2014like labels or separators\u2014within your combined result.<\/p>\n<p>Let&#8217;s say you&#8217;ve got dates in one column and event names in another. To craft a detailed narrative in a third column, your formula could be a simple &#8220;=A2&amp;&#8221; \u2013 &#8220;&amp;B2&#8221;.<\/p>\n<p><strong>STEP 1: <\/strong>In cell C2, enter the formula &#8220;=A2&amp;&#8221; &#8211; &#8220;&amp;B2&#8221; to combine the date and event names with a dash in between and press enter.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A153A35.538449_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-5.png\" alt=\"Concatenate Cells\" width=\"1063\" height=\"349\" \/><\/p>\n<p><strong>STEP 2: <\/strong>Drag the formula down to apply it to all rows in the dataset.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A153A46.017584_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-6.png\" alt=\"Concatenate Cells\" width=\"1061\" height=\"407\" \/><\/p>\n<p>It weaves in a dash between the date and event for an instant, clear report. No need for any heavyweight function, just an agile ampersand doing the heavy lifting.<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Tips_on_Concatenating_Multiple_Ranges_and_Strings\"><\/span>Tips on Concatenating Multiple Ranges and Strings<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3>The CONCAT Function for Range Concatenation<\/h3>\n<p>If your data spans across multiple cells in a range, say hello to the <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/concat-formula-in-excel\/\" target=\"_blank\" rel=\"noopener\">CONCAT function<\/a>. This gem allows you to concatenate a range of cells quickly without typing each cell reference individually. Think of it as the CONCATENATE function but with a power-up for handling arrays more efficiently.<\/p>\n<p>Just type &#8220;=CONCAT(&#8221; and select your desired range. No more separating each cell with a comma\u2014Excel does the heavy lifting. For instance, &#8220;=CONCAT(A1:A10)&#8221; would seamlessly stitch together text from ten cells in column A, transforming a list into one continuous string. It&#8217;s perfect for times when you\u2019re looking to combine large chunks of data without manual entry fuss.<\/p>\n<p><strong>STEP 1: <\/strong>Prepare your dataset with the text you want to concatenate across multiple cells in a range.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A163A14.314913_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-7.png\" alt=\"Concatenate Cells\" width=\"566\" height=\"384\" \/><\/p>\n<p><strong>STEP 2: <\/strong>In a blank cell C2, enter the formula &#8220;=CONCAT(&#8221; and select the range of cells you want to combine. Press Enter and Excel will automatically concatenate the text from the selected cells into one continuous string.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A163A23.834293_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-8.png\" alt=\"Concatenate Cells\" width=\"624\" height=\"351\" \/><\/p>\n<p><strong>STEP 3: <\/strong>Drag the formula down to apply it to all rows in the dataset.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A163A35.367827_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-9.png\" alt=\"Concatenate Cells\" width=\"719\" height=\"355\" \/><\/p>\n<p>&nbsp;<\/p>\n<h3>Handy Tricks with the TEXTJOIN Function<\/h3>\n<p>The<a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/textjoin-formula-in-excel\/\" target=\"_blank\" rel=\"noopener\"> TEXTJOIN function<\/a> is where Excel really shows off its flexibility in concatenation. This function stands out by offering two significant trump cards: You can define a delimiter to neatly separate combined texts, and choose to skip over any blank cells in your range.<\/p>\n<p>Picture needing to join a cluster of names, but some cells are empty. With TEXTJOIN, you set &#8220;=TEXTJOIN(&#8220;,&#8221;, TRUE, A1:A5)&#8221;, and the function smartly skips the blanks, leaving you with a comma-separated list that&#8217;s clean and professional.<\/p>\n<p><strong>STEP 1: <\/strong>Organize your dataset with names in cells A2 to A6.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A173A00.601053_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-10.png\" alt=\"Concatenate Cells\" width=\"410\" height=\"355\" \/><\/p>\n<p><strong>STEP 2:<\/strong> In a blank cell, enter the formula &#8220;=TEXTJOIN(&#8220;, &#8220;, TRUE, A2:A6)&#8221; to concatenate the names with a comma as a delimiter, skipping over any blank cells. Press Enter, and you&#8217;ll get a clean, comma-separated list of names without any empty cells disrupting the output.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A173A10.204145_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-11.png\" alt=\"Concatenate Cells\" width=\"921\" height=\"410\" \/><\/p>\n<p>It\u2019s particularly a game-changer when you&#8217;re dealing with data that&#8217;s not consistently filled in and you want to maintain a clean output.<\/p>\n<h2><\/h2>\n<h2><span class=\"ez-toc-section\" id=\"Common_Concatenation_Pitfalls_and_How_to_Avoid_Them\"><\/span>Common Concatenation Pitfalls and How to Avoid Them<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3>Issues When Expanding Named Tables<\/h3>\n<p>When you&#8217;re working with named tables in Excel, sometimes expanding them after using concatenation can lead to a few hitches. If you&#8217;ve set up a table that uses concatenation, and you want to add more rows, Excel might not always auto-fill your formulas correctly. This can result in <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/clear-a-ref-error-in-excel\/\" target=\"_blank\" rel=\"noopener\">#REF! errors<\/a> or incorrect data being shown.<\/p>\n<p>To avoid these issues, always ensure that any concatenation formulas are correctly referencing the table columns. Use structured references like TableName[ColumnName] within your formulas.<\/p>\n<p><strong>STEP 1: <\/strong>In a blank column cell C2, enter the formula &#8220;==[First Name]&amp; &#8221; &#8220;&amp;[Last Name]&#8221; to concatenate the names.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A173A38.845686_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-12.png\" alt=\"Concatenate Cells\" width=\"770\" height=\"548\" \/><\/p>\n<p><strong>STEP 2: <\/strong>As you add more rows to the table, Excel will automatically extend the formula to include the new data, maintaining accuracy.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A173A51.186190_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-13.gif\" alt=\"Concatenate Cells\" width=\"1056\" height=\"556\" \/><\/p>\n<p><strong>NOTE: <\/strong>Ensure all concatenation formulas reference table columns using structured references to avoid #REF! errors or incorrect data.Keep your data tidy and error-free by using structured references in concatenation formulas within named tables.<\/p>\n<p>This way, as you add more rows, Excel knows to extend the formulas using the same structure you&#8217;ve originally set up, keeping your data tidy and error-free.<\/p>\n<h3>Solving Problems with Line Breaks and Delimiters<\/h3>\n<p>Merging different data into one cell and maintaining a clean structure often requires using <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/break-line-on-worksheet\/\" target=\"_blank\" rel=\"noopener\">line breaks<\/a> or specific delimiters, especially with addresses or lists. Here&#8217;s where some might hit a snag because typing a line break into a formula isn&#8217;t straightforward.<\/p>\n<p>The solution lies in Excel\u2019s CHAR function. To insert line breaks on Windows, use CHAR(10), while on Mac, you&#8217;ll want to use CHAR(13) within your CONCATENATE or TEXTJOIN formulas. Remember to check the &#8220;Wrap text&#8221; option in the cell format settings to ensure the line breaks actually display.<\/p>\n<p><strong>STEP 1: <\/strong>In a blank cell E2, enter the formula <strong>\u201c=CONCATENATE(A2, (CHAR(47)), &#8221; &#8220;,B2,&#8221; &#8220;, CHAR(10),&#8221; &#8220;, C2, CHAR(10),&#8221; &#8220;, D2)<\/strong>&#8221; to merge the address lines with line breaks. And enter the formula.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A183A34.423584_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-14.png\" alt=\"Concatenate Cells\" width=\"1249\" height=\"343\" \/><\/p>\n<p><strong>STEP 2: <\/strong>Select the cell, go to the &#8220;Home&#8221; tab, and click on the &#8220;Wrap Text&#8221; button to ensure line breaks display correctly.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A183A46.239913_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-15.png\" alt=\"Concatenate Cells\" width=\"1377\" height=\"499\" \/><\/p>\n<p><strong>STEP 3: <\/strong>Drag the formula down to apply it to all rows in the dataset. With CHAR(10) for line breaks, (CHAR(35)) for slash and wrap text enabled, you can neatly merge address data in Excel.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A193A00.178796_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-16.png\" alt=\"Concatenate Cells\" width=\"1266\" height=\"475\" \/><\/p>\n<p>For delimiters other than line breaks, CHAR comes to the rescue again, enabling characters like double quotes (CHAR(34)), slashes (CHAR(47)), or other special characters. Armed with this knowledge, you&#8217;ll turn what could be a concatenation headache into no problem at all.<\/p>\n<h2><\/h2>\n<h2><span class=\"ez-toc-section\" id=\"Advanced_Techniques_for_Data_Combination_in_Excel\"><\/span>Advanced Techniques for Data Combination in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3>Merging Columns Row-by-Row Intelligently<\/h3>\n<p>When your goal is to merge entire columns row-by-row, you&#8217;re looking for an intelligent approach where no data gets lost. Merging columns in Excel can be child&#8217;s play with the right techniques. Suppose you&#8217;re working with three columns of data; the strategy is to use a formula that will concatenate the cells of each row across those columns, while placing a delimiter of your choice in between.<\/p>\n<p>An effective way to do this is by using the &#8220;&amp;&#8221; or CONCAT function, placed in the row where you want your results to begin. For example, the formula &#8220;=A2 &amp; &#8220;, &#8221; &amp; B2 &amp; &#8220;, &#8221; &amp; C2&#8243; merges the data from columns A, B, and C with a comma and space acting as delimiters between data points. Then, simply drag the formula down the rows to apply it to the entire column. After that, you&#8217;ve got your data combined into one tidy and easy-to-read top row.<\/p>\n<p><strong>STEP 1: <\/strong>Create a new dataset with columns for data, e.g., &#8220;Column A&#8221;, &#8220;Column B&#8221;, and &#8220;Column C&#8221;.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A193A20.232257_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-17.png\" alt=\"Concatenate Cells\" width=\"667\" height=\"361\" \/><\/p>\n<p><strong>STEP 2: <\/strong>In the top row of a new column, enter the formula &#8220;=A2 &amp; &#8220;, &#8221; &amp; B2 &amp; &#8220;, &#8221; &amp; C2&#8243; to concatenate data from each row with a delimiter, like a comma and space.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A193A31.706589_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-18.png\" alt=\"Concatenate Cells\" width=\"1016\" height=\"357\" \/><\/p>\n<p><strong>STEP 3: <\/strong>Drag the formula down to apply it to all rows in the dataset, combining the data into one tidy column. Now, each row&#8217;s data from the specified columns is merged into a single, easy-to-read column.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A193A50.472780_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-19.png\" alt=\"Concatenate Cells\" width=\"1018\" height=\"330\" \/><\/p>\n<p>&nbsp;<\/p>\n<h3>Creative Uses of CONCATENATE with Functions<\/h3>\n<p>Sometimes in Excel, you&#8217;re not just merging data\u2014you&#8217;re transforming it. CONCATENATE can play well with other functions to create more dynamic and useful formulas. Want to combine text strings with dates while keeping the date format intact? Pair CONCATENATE with the <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excels-text-function\/\" target=\"_blank\" rel=\"noopener\">TEXT function<\/a>. It lets you maintain date formatting by specifying how you want the date to appear.<\/p>\n<p>Let\u2019s say you want to join a person&#8217;s name with their birthday. The formula &#8220;=CONCATENATE(A1, &#8221; was born on &#8220;, TEXT(B1, &#8220;mmmm d, yyyy&#8221;))&#8221; will yield a sentence with the full date in a friendly, readable format. Or perhaps you&#8217;re dealing with currency values? No worries, CONCATENATE can blend those in too, ensuring that your financial data is clearly communicated with appropriately formatted figures.<\/p>\n<p><strong>STEP 1: <\/strong>Create a new dataset with columns for names (Column A) and birthdates (Column B).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A203A22.763519_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-20.png\" alt=\"Concatenate Cells\" width=\"971\" height=\"232\" \/><\/p>\n<p><strong>STEP 2: <\/strong>In a new column, enter the formula &#8220;=CONCATENATE(A2, &#8221; was born on &#8220;, TEXT(B2, &#8220;mmmm d, yyyy&#8221;))&#8221; to merge names with birthdates while preserving date format.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A203A32.367178_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-21.png\" alt=\"Concatenate Cells\" width=\"1063\" height=\"345\" \/><\/p>\n<p><strong>STEP 3: <\/strong>Drag the formula down to apply it to all rows in the dataset. Now, each row will display a sentence with the person&#8217;s name and birthdate in a friendly format.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Concatenate Cells\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2024\/04\/httpsoutranking.s3.amazonaws.com62459967_Aditi20Lundia642482982024-04-24T093A203A51.915002_Quick_Guide_How_to_Concatenate_Cells_26_Strings_in_Excel_-22.png\" alt=\"Concatenate Cells\" width=\"1065\" height=\"379\" \/><\/p>\n<h2><\/h2>\n<h2><span class=\"ez-toc-section\" id=\"FAQs\"><\/span>FAQs<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3>What Exactly is &#8220;Concatenate&#8221; in Excel?<\/h3>\n<p>In Excel, &#8220;concatenate&#8221; is a fancy term for linking or joining together. This means combining the text from different cells into one cell without altering the original values. It&#8217;s a nifty way to merge data like names and addresses or wrap up bits of information into a neat package, all while keeping your original data untouched.<\/p>\n<h3>Can You Concatenate a Range of Cells?<\/h3>\n<p>Absolutely, concatenating a range of cells is a breeze in Excel. Whether you&#8217;re using the CONCAT or TEXTJOIN function, you can reference an entire range, like A1:A10, and merge the content of those cells swiftly. It&#8217;s a time-saver when you&#8217;re tasked with combining lengthy lists or numerous data points into a single cell.<\/p>\n<h3>What Are the Alternatives to the CONCATENATE Function?<\/h3>\n<p>The go-to alternatives to the <a href=\"https:\/\/support.microsoft.com\/en-us\/office\/concatenate-function-8f8ae884-2ca8-4f7a-b093-75d702bea31d\" target=\"_blank\" rel=\"nofollow noopener\">CONCATENATE function<\/a> are the CONCAT and TEXTJOIN functions. CONCAT is like a more modern sibling to CONCATENATE, handling ranges with less fuss. TEXTJOIN steps it up a notch, allowing for delimiters and skipping empty cells. Let&#8217;s not forget the simple ampersand (&#8220;&amp;&#8221;), a quick-fire way to link text strings without a full-fledged function.<\/p>\n<h3>How to Concatenate Cells with Different Delimiters?<\/h3>\n<p>To concatenate <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/how-to-merge-cells\/\" target=\"_blank\" rel=\"noopener\">cells with various delimiters in<\/a> Excel, you can mix and match within your formula. Whether you&#8217;re partial to commas, semicolons, or spaces, you wrap the character in quotes and place it between cell references using either the CONCATENATE function, the ampersand (\u201c&amp;\u201d), or the TEXTJOIN function, which is explicitly designed to insert custom delimiters as you merge cells.<\/p>\n<h3>How do I CONCATENATE two columns in Excel?<\/h3>\n<p>To concatenate two columns in Excel, simply use the CONCATENATE function or the ampersand (\u201c&amp;\u201d) in the cell where you want to display the combined data. If you have values in columns A and B, input &#8220;=CONCATENATE(A2, &#8221; &#8220;, B2)&#8221; or &#8220;=A2 &amp; &#8221; &#8221; &amp; B2&#8243; for a space between the merged data. Drag the corner of the cell downwards to apply this to the entire column.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Learn essential Excel skills: Combine cells &amp; strings effortlessly with our guide to CONCATENATE, TEXTJOIN, &amp; more for sleek data management.<\/p>\n","protected":false},"author":1,"featured_media":44183,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"How to Concatenate Cells and Strings in Excel - Step by Step Guide","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[1332,3],"tags":[554,2520,618],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/43936"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=43936"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/43936\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/44183"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=43936"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=43936"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=43936"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}