{"id":29180,"date":"2023-06-02T16:08:15","date_gmt":"2023-06-02T14:08:15","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=29180"},"modified":"2024-03-22T18:32:10","modified_gmt":"2024-03-22T17:32:10","slug":"split-string-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/split-string-in-excel\/","title":{"rendered":"5 Best Ways to Split String in Excel (Free Examples)"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/4F786FE9-F4E8-4992-8A39-D15EC2B14413.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-29308\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/4F786FE9-F4E8-4992-8A39-D15EC2B14413-1024x576.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"1024\" height=\"576\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/4F786FE9-F4E8-4992-8A39-D15EC2B14413-1024x576.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/4F786FE9-F4E8-4992-8A39-D15EC2B14413-300x169.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/4F786FE9-F4E8-4992-8A39-D15EC2B14413-768x432.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/4F786FE9-F4E8-4992-8A39-D15EC2B14413.png 1280w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a>Many times, users are provided with unformatted and unclean data to work on.<\/p>\n<p>It can be really difficult to apply filters, sort, or even create <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-pivot-tables\/\" target=\"_blank\" rel=\"noopener\">Pivot Tables<\/a> using those unstructured data.<\/p>\n<p>Microsoft Excel has a variety of <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/free-microsoft-excel-online-course\/\" target=\"_blank\" rel=\"noopener\">functions and features<\/a> that can be used to split those combined texts into separate columns and make data management more efficient.<\/p>\n<p>In this article, we will be covering the 5 best ways to split string in Excel with our Free Step By Step Examples &amp; Practice Workbook!<\/p>\n<p>Let\u2019s look at these methods thoroughly!<\/p>\n<h4><strong>Download the Excel Workbook below to follow along and understand how to split string in Excel \u2013<\/strong><\/h4>\n<h4><strong><a class=\"IRPP_minimalist icon-download\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Split-String-in-Excel.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<\/span><span class=\"postTitle px-3\">Split-String-in-Excel.xlsx<\/span><\/a><\/strong><\/h4>\n<div>\n<div>\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\/split-string-in-excel\/#Method_1_Text_to_Column\" title=\"Method 1: Text to Column\">Method 1: Text to Column<\/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\/split-string-in-excel\/#Method_2_LEFT_RIGHT_MID_Function\" title=\"Method 2: LEFT, RIGHT &amp; MID Function\">Method 2: LEFT, RIGHT &amp; MID Function<\/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\/split-string-in-excel\/#Method_3_Text_Functions\" title=\"Method 3: Text Functions\">Method 3: Text Functions<\/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\/split-string-in-excel\/#Method_4_TEXTSPLIT_Function\" title=\"Method 4: TEXTSPLIT Function\">Method 4: TEXTSPLIT Function<\/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\/split-string-in-excel\/#Method_5_Flash_Fill\" title=\"Method 5: Flash Fill\">Method 5: Flash Fill<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Method_1_Text_to_Column\"><\/span><b><span id=\"1\" style=\"color: #ff0000!important;\">Method 1: Text to Column<\/span><\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<\/div>\n<\/div>\n<p>Ever encountered a situation where you have a <strong>data set of employee full names in one column and want to split them<\/strong> into separate columns? No worries. Excel has an excellent ad-hoc solution to this &#8211;<strong> Text to Column<\/strong>.<\/p>\n<p>This feature <strong>allows you to split text into separate columns using a delimiter<\/strong> like a comma, space, semi-column, etc. Follow the steps below to know how to split string in Excel using Text to Columns &#8211;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 1: <\/span><\/strong>Select the range containing the full names of the employees.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-281.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-29217\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-281.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"444\" height=\"560\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-281.png 538w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-281-238x300.png 238w\" sizes=\"(max-width: 444px) 100vw, 444px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 2: <\/span><\/strong>Go to <strong>Data<\/strong> &gt; <strong>Text to Columns<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-282.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-29218 size-full\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-282.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"1392\" height=\"161\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-282.png 1392w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-282-300x35.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-282-1024x118.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-282-768x89.png 768w\" sizes=\"(max-width: 1392px) 100vw, 1392px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3: <\/span><\/strong>In the Convert Text to Columns dialog box, select <strong>Delimited<\/strong> &gt; <strong>Next<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-283.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-29219\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-283.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"648\" height=\"464\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-283.png 749w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-283-300x215.png 300w\" sizes=\"(max-width: 648px) 100vw, 648px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 4: <\/span><\/strong>Select the <strong>delimiter<\/strong> for the data. Here, it is <strong>Space<\/strong>. Check the <strong>Data Preview<\/strong> at the bottom and then click <strong>Next<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-284.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29220\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-284.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"749\" height=\"537\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-284.png 749w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-284-300x215.png 300w\" sizes=\"(max-width: 749px) 100vw, 749px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 5: <\/span><\/strong>Select cell $B$2 as the <strong>destination<\/strong> and click <strong>Next<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-286.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29221\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-286.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"747\" height=\"534\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-286.png 747w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-286-300x214.png 300w\" sizes=\"(max-width: 747px) 100vw, 747px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 6: <\/span><\/strong>Select <strong>Finish.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-287.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29222\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-287.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"750\" height=\"535\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-287.png 750w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-287-300x214.png 300w\" sizes=\"(max-width: 750px) 100vw, 750px\" \/><\/a><\/p>\n<p>The full name of the employees are now separated into 2 columns &#8211; First name and Last name.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-288.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29223\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-288.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"551\" height=\"683\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-288.png 551w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-288-242x300.png 242w\" sizes=\"(max-width: 551px) 100vw, 551px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Method_2_LEFT_RIGHT_MID_Function\"><\/span><b><span id=\"2\" style=\"color: #ff0000!important;\">Method 2: LEFT, RIGHT &amp; MID Function<\/span><\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>In this example, we have employee data in the following format &#8211; <strong>AL56442020<\/strong><\/p>\n<ul>\n<li>AL &#8211; Employee&#8217;s initials<\/li>\n<li>5644- Employee ID<\/li>\n<li>2020- Year of joining<\/li>\n<\/ul>\n<p>We can use <strong>LEFT, RIGHT, and MID<\/strong> functions to extract all three parts of this data. Follow the steps below to know how to split string in Excel &#8211;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 1: <\/span><\/strong>Enter the <strong>LEFT<\/strong> function. This function is used to extract the <strong>employee&#8217;s initial<\/strong> i.e. <strong>first 2 characters<\/strong> from the text.<\/p>\n<p><strong>=LEFT(A2,2)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-289.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29225\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-289.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"658\" height=\"599\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-289.png 658w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-289-300x273.png 300w\" sizes=\"(max-width: 658px) 100vw, 658px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 2: <\/span><\/strong>Enter the <strong>MID<\/strong> function. This function is used to extract the <strong>employee&#8217;s id<\/strong> i.e. <strong>4 characters starting from the 3rd position<\/strong> of the text.<\/p>\n<p><strong>=MID(A2,3,4)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-290.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29226\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-290.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"697\" height=\"598\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-290.png 697w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-290-300x257.png 300w\" sizes=\"(max-width: 697px) 100vw, 697px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3: <\/span><\/strong>Enter the <strong>RIGHT<\/strong> function. This function is used to extract the<strong> employee&#8217;s year of joining<\/strong> i.e. <strong>4 characters from the end<\/strong> of the text.<\/p>\n<p><strong>=RIGHT(A2,4)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-291.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29227\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-291.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"691\" height=\"600\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-291.png 691w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-291-300x260.png 300w\" sizes=\"(max-width: 691px) 100vw, 691px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Method_3_Text_Functions\"><\/span><b><span id=\"3\" style=\"color: #ff0000!important;\">Method 3: Text Functions<\/span><\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If you want to check <strong>where a specific text is located<\/strong> in the source text, it is very easy to search for the position using the\u00a0<strong>FIND <a class=\"wpil_keyword_link\" title=\"Formula\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" data-wpil-keyword-link=\"linked\">Formula<\/a><\/strong>!<\/p>\n<p>In this example, we have the <strong>first name and employee id, and year of joining separated by a line break<\/strong>.\u00a0<strong>CHAR(10)<\/strong> in Excel is used to get a line break in a cell. We will be using the FIND function to get the position of the line break.<\/p>\n<p>Follow the steps below to learn how to split string in Excel to get them in separate columns. &#8211;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 1: <\/span><\/strong>Enter the following formula to get the<strong> first name<\/strong> i.e. <strong>all characters before the 1st instance of line break<\/strong>.<\/p>\n<p><strong>=LEFT(A2,FIND(CHAR(10),A2)-1)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-305.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29242\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-305.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"754\" height=\"670\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-305.png 754w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-305-300x267.png 300w\" sizes=\"(max-width: 754px) 100vw, 754px\" \/><\/a><\/p>\n<ul>\n<li>Here, the<strong> FIND function<\/strong> will provide you with the position of the 1st line break.<\/li>\n<li>We will <strong>subtract 1<\/strong> because we do not need the line break in your result.<\/li>\n<li>The <strong>LEFT function<\/strong> will extract all characters left to this position.<\/li>\n<\/ul>\n<p><strong><span style=\"color: #ff0000;\">STEP 2: <\/span><\/strong>Enter the following formula to get the <strong>region<\/strong> i.e. all characters in between the 1st and 2nd line break.<\/p>\n<p><strong>=MID(A2,FIND(CHAR(10),A2)+1,FIND(CHAR(10),A2,FIND(CHAR(10),A2)+1)-FIND(CHAR(10),A2)-1)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-306.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29243\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-306.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"974\" height=\"672\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-306.png 974w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-306-300x207.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-306-768x530.png 768w\" sizes=\"(max-width: 974px) 100vw, 974px\" \/><\/a><\/p>\n<p>Here, the MID function will provide the region. It has three arguments &#8211;<\/p>\n<ul>\n<li><strong>text<\/strong> &#8211; It is the source text located in cell A2.<\/li>\n<li><strong>start_num<\/strong> &#8211; It is the starting position for the MID function. This will be the position of 1st line break, plus 1 i.e. FIND(CHAR(10),A2) + 1.<\/li>\n<li><strong>num_chars<\/strong> &#8211; It is the number of characters that need to be extracted. This will be the difference between the position of 2nd line break and 1st line break, minus 1 i.e. FIND(CHAR(10),A2,FIND(CHAR(10),A2)+1)-FIND(CHAR(10),A2)-1<\/li>\n<\/ul>\n<p><strong><span style=\"color: #ff0000;\">STEP 3: <\/span><\/strong>Enter the following formula to get the <strong>sales amount<\/strong> i.e. all characters after the 3rd line break.<\/p>\n<p><strong>=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2,FIND(CHAR(10),A2)+1))<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-307.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29244\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-307.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"850\" height=\"664\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-307.png 850w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-307-300x234.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-307-768x600.png 768w\" sizes=\"(max-width: 850px) 100vw, 850px\" \/><\/a><\/p>\n<p>Here, the RIGHT function will provide the region. It has three arguments &#8211;<\/p>\n<ul>\n<li><strong>text<\/strong> &#8211; It is the source text located in cell A2.<\/li>\n<li><strong>num_chars<\/strong> &#8211; It is the number of characters that need to be extracted. This will be the difference between the length of the text and position of 2nd line break, plus 1 i.e. LEN(A2)-FIND(CHAR(10),A2,FIND(CHAR(10),A2)+1<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Method_4_TEXTSPLIT_Function\"><\/span><strong><span id=\"4\" style=\"color: #ff0000!important;\">Method 4: TEXTSPLIT Function<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>TEXTSPLIT function can be used <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/opposite-of-concatenate\/\" target=\"_blank\" rel=\"noopener\">to split string in Excel<\/a><strong> by a given delimiter<\/strong> and <strong>display the result in multiple cells<\/strong>. It can split the text into both <strong>columns as well as rows<\/strong>. It is like a formula version of the Text-to-Column feature in Excel but it is much more versatile.<\/p>\n<p>This function is exclusively available for <a href=\"https:\/\/www.microsoft.com\/en-in\/microsoft-365\/business\/compare-all-microsoft-365-business-products-b?ef_id=_k_CjwKCAjw9pGjBhB-EiwAa5jl3Oh18CnhLhocLVu_mqG_wAXoCc9PmYkFBkaVlkw6SisXqCk_PtCH3BoCRXoQAvD_BwE_k_&amp;OCID=AIDcmmg9qnxz0z_SEM__k_CjwKCAjw9pGjBhB-EiwAa5jl3Oh18CnhLhocLVu_mqG_wAXoCc9PmYkFBkaVlkw6SisXqCk_PtCH3BoCRXoQAvD_BwE_k_&amp;gclid=CjwKCAjw9pGjBhB-EiwAa5jl3Oh18CnhLhocLVu_mqG_wAXoCc9PmYkFBkaVlkw6SisXqCk_PtCH3BoCRXoQAvD_BwE\" target=\"_blank\" rel=\"nofollow noopener\"><strong>Excel 365 subscribers<\/strong><\/a>.<\/p>\n<p>The <strong>syntax<\/strong> of this function is &#8211;<\/p>\n<p>=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])<\/p>\n<ul>\n<li><strong>text<\/strong> &#8211; The text you want to split. Required.<\/li>\n<li><strong>col_delimiter<\/strong> &#8211; The text that marks the point where to spill the text across columns.<\/li>\n<li><strong>row_delimiter<\/strong> &#8211; The text that marks the point where to spill the text down rows. Optional.<\/li>\n<li><strong>ignore_empty<\/strong> &#8211; Specify TRUE to ignore consecutive delimiters. Defaults to FALSE, which creates an empty cell. Optional.<\/li>\n<li><strong>match_mode<\/strong> &#8211; Specify 1 to perform a case-insensitive match. Defaults to 0, which does a case-sensitive match. Optional.<\/li>\n<li><strong>pad_with<\/strong> &#8211; The value with which to pad the result. The default is #N\/A.<\/li>\n<\/ul>\n<p>In this example, let us see how to split string in Excel using the TEXTSPLIT function.<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 1: <\/span><\/strong>Enter the <strong>TEXTSPLIT<\/strong> formula &#8211;<\/p>\n<p><strong>=TEXTSPLIT<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-294.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29231\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-294.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"1229\" height=\"230\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-294.png 1229w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-294-300x56.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-294-1024x192.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-294-768x144.png 768w\" sizes=\"(max-width: 1229px) 100vw, 1229px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 2: <\/span><\/strong>Enter the 1st argument i.e. <strong>text<\/strong>.<\/p>\n<p><strong>=TEXTSPLIT(B1,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-295.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29232\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-295.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"1237\" height=\"249\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-295.png 1237w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-295-300x60.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-295-1024x206.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-295-768x155.png 768w\" sizes=\"(max-width: 1237px) 100vw, 1237px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3: <\/span><\/strong>Enter the 2nd argument i.e. <strong>col_delimiter<\/strong>. Here, it is <strong>left blank<\/strong> as we will split the text row-wise not column-wise.<\/p>\n<p><strong>=TEXTSPLIT(B1,,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-296.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29233\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-296.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"1237\" height=\"225\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-296.png 1237w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-296-300x55.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-296-1024x186.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-296-768x140.png 768w\" sizes=\"(max-width: 1237px) 100vw, 1237px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 4: <\/span><\/strong>Enter the 3rd argument i.e. <strong>row_delimiter<\/strong>. Here, it is <strong>CHAR(10)<\/strong>.<\/p>\n<p><strong>=TEXTSPLIT(B1,,CHAR(10))<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-297.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29234\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-297.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"1232\" height=\"248\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-297.png 1232w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-297-300x60.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-297-1024x206.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-297-768x155.png 768w\" sizes=\"(max-width: 1232px) 100vw, 1232px\" \/><\/a><\/p>\n<p>Repeat the same steps for the region and salary columns as well. The three rows will automatically be filled with the desired result!<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-299.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29236\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-299.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"1228\" height=\"272\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-299.png 1228w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-299-300x66.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-299-1024x227.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-299-768x170.png 768w\" sizes=\"(max-width: 1228px) 100vw, 1228px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Method_5_Flash_Fill\"><\/span><strong><span id=\"5\" style=\"color: #ff0000!important;\">Method 5: Flash Fill<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><strong>Flash Fill<\/strong> in Excel is a feature that was introduced in Excel 2013. It is very handy as <strong>Excel predicts the rest of your inputs based on the first entry that you have placed<\/strong>. Once its prediction is correct and you confirm it, it will fill the rest of the rows literally\u00a0<strong>in a flash!<\/strong><\/p>\n<p>The cool thing with Excel\u2019s\u00a0<strong>Flash Fill<\/strong>\u00a0is there is no need to use\u00a0<a class=\"wpil_keyword_link\" title=\"formulas\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" data-wpil-keyword-link=\"linked\">formulas<\/a> and removes manual repetitiveness, saving you heaps of time in the process!<\/p>\n<p>Follow the steps below to understand how to use Flash Fill to split string in Excel &#8211;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 1: <\/span>Type the first name<\/strong> in cell B2.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-300.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29237\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-300.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"621\" height=\"677\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-300.png 621w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-300-275x300.png 275w\" sizes=\"(max-width: 621px) 100vw, 621px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 2: <\/span>Select the range<\/strong> where you need the first names to be displayed. Here, it is B2:B27.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-301.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29238\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-301.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"623\" height=\"631\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-301.png 623w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-301-296x300.png 296w\" sizes=\"(max-width: 623px) 100vw, 623px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3: <\/span><\/strong>Go to <strong>Home<\/strong> &gt; <strong>Fill<\/strong> &gt; <strong>Flash Fill<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-302.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29239\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-302.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"1754\" height=\"432\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-302.png 1754w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-302-300x74.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-302-1024x252.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-302-768x189.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-302-1536x378.png 1536w\" sizes=\"(max-width: 1754px) 100vw, 1754px\" \/><\/a><\/p>\n<p>All the first names will be filled automatically.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-303.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29240\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-303.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"701\" height=\"645\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-303.png 701w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-303-300x276.png 300w\" sizes=\"(max-width: 701px) 100vw, 701px\" \/><\/a><\/p>\n<p>Repeat the steps for the region and salary columns as well. This will split string in Excel.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-304.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29241\" title=\"5 Effective Ways to Split String in Excel for Better Data Management\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-304.png\" alt=\"5 Effective Ways to Split String in Excel for Better Data Management\" width=\"713\" height=\"629\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-304.png 713w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/05\/Screenshot-304-300x265.png 300w\" sizes=\"(max-width: 713px) 100vw, 713px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Many times, users are provided with unformatted and unclean data to work on. It can be really difficult to apply filters, sort, or even create Pivot Tables using those unstructured data. Microsoft Excel has a variety of functions and features that can be used to split those combined texts into separate columns and make data [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":29308,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[483,300,302,484,305,14],"tags":[1243,1240,1241,1242],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/29180"}],"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=29180"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/29180\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/29308"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=29180"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=29180"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=29180"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}