{"id":69,"date":"2018-06-27T14:48:44","date_gmt":"2018-06-27T14:48:44","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=69"},"modified":"2024-03-28T11:55:10","modified_gmt":"2024-03-28T11:55:10","slug":"opposite-of-concatenate","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/","title":{"rendered":"Opposite of Concatenate in Excel"},"content":{"rendered":"\n<p>The&nbsp;<strong>CONCATENATE<\/strong>&nbsp;function is used in Excel to merge several strings into one.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/concatenate.png\" alt=\"\" class=\"wp-image-73\"\/><\/figure>\n\n\n\n<p>Let\u2019s suppose that you want to reverse the process and change the&nbsp;<strong>&#8220;JohnDoe45&#8221;<\/strong>&nbsp;to&nbsp;<strong>&#8220;John&#8221;<\/strong>,&nbsp;<strong>&#8220;Doe&#8221;<\/strong>&nbsp;and<strong>&nbsp;&#8220;45&#8221;<\/strong>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Text to Columns<\/h2>\n\n\n\n<p>In the following example, we will try to split the text into three parts, first in cell&nbsp;<strong>A1,<\/strong>&nbsp;second in cell&nbsp;<strong>B1,<\/strong>&nbsp;and third in cell&nbsp;<strong>C1.<\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">CAUTION<\/h3>\n\n\n\n<p>Create a copy of the string, before you use this technique. The first word will be in cell A1 and not B1, so it will overwrite the string.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/opposite-of-concatenate.png\" alt=\"\" class=\"wp-image-71\"\/><\/figure>\n\n\n\n<p>Click cell&nbsp;<strong>A1<\/strong>&nbsp;and go to&nbsp;<strong>DATA<\/strong><strong> &gt;&gt; Data Tools &gt;&gt; <a href=\"https:\/\/officetuts.net\/excel\/training\/splitting-text-using-text-to-columns-wizard\/\">Text to Columns<\/a><\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/fixed-width.png\" alt=\"\" class=\"wp-image-75\"\/><\/figure>\n\n\n\n<p>Usually, we choose&nbsp;<strong>Delimited,<\/strong>&nbsp;to split characters by some kind of separators, such as space, tab, or comma. In our example, we don\u2019t have any separator between words so we will use the second options, which is&nbsp;<strong>Fixed Width<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/set-fixed-width.png\" alt=\"\" class=\"wp-image-72\"\/><\/figure>\n\n\n\n<p>Here, you can set how you want to split the words. Click&nbsp;<strong>Finish<\/strong>&nbsp;to accept.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"293\" height=\"60\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/end-result.png\" alt=\"\" class=\"wp-image-224\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Functions<\/h2>\n\n\n\n<p>Another way to split text is by using the following functions:&nbsp;<strong>LEFT, MID, <\/strong>and <strong>RIGHT.<\/strong>&nbsp;Choose&nbsp;<strong><span class=\"shortcut\">Ctrl + `<\/span><\/strong>&nbsp;to see the formulas.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/functions.png\" alt=\"\" class=\"wp-image-76\"\/><\/figure>\n\n\n\n<p>Use the shortcut one more time it will give the following effect.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/functions-result.png\" alt=\"\" class=\"wp-image-70\"\/><\/figure>\n\n\n\n<ol>\n<li><strong>=LEFT(A1,4)<\/strong>&nbsp;\u2013 take the first four elements from cell&nbsp;<strong>A1.<\/strong><\/li>\n\n\n\n<li><strong>=MID(A1,5,3)<\/strong>&nbsp;\u2013 take three elements from cell&nbsp;<strong>A1<\/strong>&nbsp;starting from the fifth element.<\/li>\n\n\n\n<li><strong>=RIGHT(A1,2)<\/strong>&nbsp;\u2013 take the last two elements from cell&nbsp;<strong>A1.<\/strong><\/li>\n<\/ol>\n\n\n\n<p>You can create the VBA code that will split the whole string at once.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub SplitText()\n    ActiveCell.Offset(0, 1).Select\n    ActiveCell.FormulaR1C1 = \"=LEFT(RC[-1],4)\"\n    ActiveCell.Offset(0, 1).Select\n    ActiveCell.FormulaR1C1 = \"=MID(RC[-2],5,3)\"\n    ActiveCell.Offset(0, 1).Select\n    ActiveCell.FormulaR1C1 = \"=RIGHT(RC[-3],2)\"\nEnd Sub<\/pre>\n\n\n\n<p><strong>Code analysis<\/strong><\/p>\n\n\n\n<p><strong>2.<\/strong>&nbsp;Let\u2019s move the active cell in one position to the right because it will be the place where we enter the first part of the string.<\/p>\n\n\n\n<p><strong>3.<\/strong>&nbsp;This part of the code will insert the following formula&nbsp;<strong>=LEFT(A1, 4)<\/strong>.&nbsp;<strong>RC[-1]<\/strong>&nbsp;means that it will use the text from the cell to the left (<strong>A1<\/strong>) of the active cell (<strong>B1<\/strong>).<\/p>\n\n\n\n<p><strong>4-7.<\/strong>&nbsp;Here, the code inserts functions:&nbsp;<strong>MID<\/strong>&nbsp;and&nbsp;<strong>RIGHT,<\/strong>&nbsp;&nbsp;and moves the cells accordingly.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The&nbsp;CONCATENATE&nbsp;function is used in Excel to merge several strings into one. Let\u2019s suppose that you want to reverse the process and change the&nbsp;&#8220;JohnDoe45&#8221;&nbsp;to&nbsp;&#8220;John&#8221;,&nbsp;&#8220;Doe&#8221;&nbsp;and&nbsp;&#8220;45&#8221;&#8230;.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[190],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Opposite of Concatenate in Excel<\/title>\n<meta name=\"description\" content=\"CONCATENATE function to merge strings. Discover how to split text into separate cells using Text to Columns or LEFT, MID, and RIGHT functions.\" \/>\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\/examples\/opposite-of-concatenate\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Opposite of Concatenate in Excel\" \/>\n<meta property=\"og:description\" content=\"CONCATENATE function to merge strings. Discover how to split text into separate cells using Text to Columns or LEFT, MID, and RIGHT functions.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-27T14:48:44+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-28T11:55:10+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/concatenate.png\" \/>\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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"Opposite of Concatenate in Excel\",\"datePublished\":\"2018-06-27T14:48:44+00:00\",\"dateModified\":\"2024-03-28T11:55:10+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/\"},\"wordCount\":366,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/concatenate.png\",\"keywords\":[\"pinterest\"],\"articleSection\":[\"examples\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/\",\"url\":\"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/\",\"name\":\"Opposite of Concatenate in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/concatenate.png\",\"datePublished\":\"2018-06-27T14:48:44+00:00\",\"dateModified\":\"2024-03-28T11:55:10+00:00\",\"description\":\"CONCATENATE function to merge strings. Discover how to split text into separate cells using Text to Columns or LEFT, MID, and RIGHT functions.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/concatenate.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/concatenate.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Opposite of Concatenate 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":"Opposite of Concatenate in Excel","description":"CONCATENATE function to merge strings. Discover how to split text into separate cells using Text to Columns or LEFT, MID, and RIGHT functions.","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\/examples\/opposite-of-concatenate\/","og_locale":"en_US","og_type":"article","og_title":"Opposite of Concatenate in Excel","og_description":"CONCATENATE function to merge strings. Discover how to split text into separate cells using Text to Columns or LEFT, MID, and RIGHT functions.","og_url":"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/","article_published_time":"2018-06-27T14:48:44+00:00","article_modified_time":"2024-03-28T11:55:10+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/concatenate.png"}],"author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"Opposite of Concatenate in Excel","datePublished":"2018-06-27T14:48:44+00:00","dateModified":"2024-03-28T11:55:10+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/"},"wordCount":366,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/concatenate.png","keywords":["pinterest"],"articleSection":["examples"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/","url":"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/","name":"Opposite of Concatenate in Excel","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/concatenate.png","datePublished":"2018-06-27T14:48:44+00:00","dateModified":"2024-03-28T11:55:10+00:00","description":"CONCATENATE function to merge strings. Discover how to split text into separate cells using Text to Columns or LEFT, MID, and RIGHT functions.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/concatenate.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/concatenate.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/examples\/opposite-of-concatenate\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Opposite of Concatenate 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\/69"}],"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=69"}],"version-history":[{"count":13,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/69\/revisions"}],"predecessor-version":[{"id":17375,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/69\/revisions\/17375"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=69"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=69"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=69"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}