{"id":3969,"date":"2019-01-14T12:31:07","date_gmt":"2019-01-14T12:31:07","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=3969"},"modified":"2024-03-29T14:24:53","modified_gmt":"2024-03-29T14:24:53","slug":"concatenate-vs-ampersand","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/","title":{"rendered":"Concatenate vs &#038; in Excel"},"content":{"rendered":"\n<div class=\"wp-block-file\"><a id=\"wp-block-file--media-3fbc7df4-c52d-4de9-a717-df61d8ea3348\" href=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/01\/Concatenate-vs-.xlsx\">Concatenate-vs-ampersand<\/a><a href=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/01\/Concatenate-vs-.xlsx\" class=\"wp-block-file__button wp-element-button\" download aria-describedby=\"wp-block-file--media-3fbc7df4-c52d-4de9-a717-df61d8ea3348\">Download File<\/a><\/div>\n\n\n\n<p>When working with Excel, you probably noticed that there are two methods of joining strings. One of them is by using the <strong>ampersand sign<\/strong> (&amp;) and the other way is to use the <strong>CONCATENATE<\/strong> function.<\/p>\n\n\n\n<p>The following formulas will return the same result.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=\"This \"&amp;\"is \"&amp;\"text\"<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">=CONCATENATE(\"This \",\"is \",\"text\")<\/pre>\n\n\n\n<p>Both will return \u201cThis is text\u201d.<\/p>\n\n\n\n<p>You can also create more arguments by joining spaces as separate arguments with CONCATENATE.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=CONCATENATE(\"This\",\" \",\"is\",\" \",\"text\")<\/pre>\n\n\n\n<p>You can also add spaces as separate words.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=\"This\"&amp;\" \"&amp;\"is\"&amp;\" \"&amp;\"text\"<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">TEXTJOIN function<\/h2>\n\n\n\n<p>A separator (in our case space) can add too much complexity to our formula. In Office 365 and Excel 2019, there is a new function called <strong>TEXTJOIN<\/strong>. It\u2019s very similar to <strong>CONCATENATE<\/strong>, but instead of adding additional arguments as separators, you can add it only once, at the beginning of the formula.<\/p>\n\n\n\n<p>Both formulas return the same result.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=CONCATENATE(\"This\",\" \",\"is\",\" \",\"text\")<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">=TEXTJOIN(\" \",TRUE,\"This\",\"is\",\"text\")<\/pre>\n\n\n\n<p>The usefulness of the <strong>TEXTJOIN<\/strong> function is more visible when you have more arguments, especially when these arguments can be referenced as a range.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=TEXTJOIN(\" \",TRUE,A1:A10)<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Limitation of the CONCATENATE function<\/h2>\n\n\n\n<p>In the older Excel versions, the CONCATENATION function was limited to 30 arguments, but now it offers 255. I don\u2019t think this is as important now as it used to be.<\/p>\n\n\n\n<p>Joining text with ampersand doesn\u2019t have this limitation.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Combining both for clearer formulas<\/h2>\n\n\n\n<p>Sometimes, using both ways together create a formula that is easier to read.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=CONCATENATE(FirstName,\" \",LastName) &amp; CHAR(10) &amp; CONCATENATE(Age, City, Occupation)<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Compatibility reasons<\/h2>\n\n\n\n<p>The last reason to have both ways is for historical and compatibility reasons. Older versions of Excel don\u2019t support one method, and different spreadsheets like Apple Numbers, Google Docs, Open Office, or LibreOffice don\u2019t support the other one.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When working with Excel, you probably noticed that there are two methods of joining strings. One of them is by using the ampersand&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[170,190],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Concatenate vs &amp; in Excel<\/title>\n<meta name=\"description\" content=\"Learn about joining strings in Excel using the ampersand sign and the CONCATENATE function. Download the file and try these formulas.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Concatenate vs &amp; in Excel\" \/>\n<meta property=\"og:description\" content=\"Learn about joining strings in Excel using the ampersand sign and the CONCATENATE function. Download the file and try these formulas.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/\" \/>\n<meta property=\"article:published_time\" content=\"2019-01-14T12:31:07+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-29T14:24:53+00:00\" \/>\n<meta name=\"author\" content=\"Tomasz Decker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tomasz Decker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"Concatenate vs &#038; in Excel\",\"datePublished\":\"2019-01-14T12:31:07+00:00\",\"dateModified\":\"2024-03-29T14:24:53+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/\"},\"wordCount\":265,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"keywords\":[\"file\",\"pinterest\"],\"articleSection\":[\"formulas\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/\",\"url\":\"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/\",\"name\":\"Concatenate vs & in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"datePublished\":\"2019-01-14T12:31:07+00:00\",\"dateModified\":\"2024-03-29T14:24:53+00:00\",\"description\":\"Learn about joining strings in Excel using the ampersand sign and the CONCATENATE function. Download the file and try these formulas.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Concatenate vs &#038; in Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Concatenate vs & in Excel","description":"Learn about joining strings in Excel using the ampersand sign and the CONCATENATE function. Download the file and try these formulas.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/","og_locale":"en_US","og_type":"article","og_title":"Concatenate vs & in Excel","og_description":"Learn about joining strings in Excel using the ampersand sign and the CONCATENATE function. Download the file and try these formulas.","og_url":"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/","article_published_time":"2019-01-14T12:31:07+00:00","article_modified_time":"2024-03-29T14:24:53+00:00","author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"Concatenate vs &#038; in Excel","datePublished":"2019-01-14T12:31:07+00:00","dateModified":"2024-03-29T14:24:53+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/"},"wordCount":265,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"keywords":["file","pinterest"],"articleSection":["formulas"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/","url":"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/","name":"Concatenate vs & in Excel","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"datePublished":"2019-01-14T12:31:07+00:00","dateModified":"2024-03-29T14:24:53+00:00","description":"Learn about joining strings in Excel using the ampersand sign and the CONCATENATE function. Download the file and try these formulas.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/formulas\/concatenate-vs-ampersand\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Concatenate vs &#038; in Excel"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/3969"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=3969"}],"version-history":[{"count":2,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/3969\/revisions"}],"predecessor-version":[{"id":13628,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/3969\/revisions\/13628"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=3969"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=3969"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=3969"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}