{"id":4257,"date":"2023-02-13T15:36:21","date_gmt":"2023-02-13T15:36:21","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=4257"},"modified":"2024-03-19T15:39:01","modified_gmt":"2024-03-19T15:39:01","slug":"text-to-column-multiple-ways-to-split-text-to-column-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/text-to-column-multiple-ways-to-split-text-to-column-in-excel\/","title":{"rendered":"Text to Column &#8211; Multiple ways to Split Text to Column in Excel"},"content":{"rendered":"<p>Split Text to Column in Excel is a crucial feature for anyone looking to enhance their data organization and analysis. By mastering this tool, you can transform cumbersome, mixed data into structured, easy-to-analyze formats, streamlining your workflows and uncovering insights more quickly. Whether you&#8217;re reformatting imported data, segregating names and addresses, or preparing reports, the ability to split text into columns is indispensable. Embrace this function to make your Excel spreadsheets more versatile and your data manipulation tasks more efficient.<\/p>\n<p><strong>This Tutorial Covers:<\/strong><\/p>\n<ol>\n<li><strong>Text to Columns in Excel: Where to Find Them<\/strong>\n<ul>\n<li><a href=\"#example-1\u2013-split-first-and-last-names-in-names\">Example 1 \u2013 Split first and last names in names<\/a><\/li>\n<li><a href=\"#example2\u2013split-email-addresses-by-username-and-domain-name\">Example 2 \u2013 Split email addresses by username and domain name<\/a><\/li>\n<li><a href=\"#example-3\u2013from-the-url,-obtain-the-root-domain\">Example 3 \u2013 from the URL, obtain the Root Domain<\/a><\/li>\n<li><a href=\"#example-4\u2013transform-unreliable-date-formats-into-reliable-date-formats\">Example 4 \u2013 Transform Unreliable Date Formats Into Reliable Date Formats<\/a><\/li>\n<li><a href=\"#example-5\u2013convert-text-to-numbers\">Example 5 \u2013 Convert Text to Numbers<\/a><\/li>\n<li><a href=\"#example-6\u2013-extract-first-five-characters-of-a-string\">Example 6 \u2013 Extract First five Characters of a String<\/a><\/li>\n<li><a href=\"#example-7\u2013negative-integers-from-numbers-with-a-trailing-minus-sign\">Example 7 \u2013 Negative integers from numbers with a trailing minus sign<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<h2><strong>1. Text to Columns in Excel: Where to Find Them<\/strong><\/h2>\n<p>To access Text to Columns, select the dataset and go to \u201cData\u201d tab and then click \u201cText to Columns\u201d under \u201cData Tools\u201d section.<\/p>\n<p><img fetchpriority=\"high\" decoding=\"async\" class=\"aligncenter wp-image-4258 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-1.png\" alt=\"Split Text to Column in Excel\" width=\"569\" height=\"114\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-1.png 569w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-1-300x60.png 300w\" sizes=\"(max-width: 569px) 100vw, 569px\" \/><\/p>\n<p>The Convert Text to Columns Wizard would then be displayed.<\/p>\n<p>Before dividing the text into columns, this wizard goes through three steps and requests feedback from the user (you will see how these different options can be used in the examples below).<\/p>\n<p>You may also use the keyboard shortcut <strong>ALT + A + E<\/strong> to get to Text to Columns.<\/p>\n<p>Let&#8217;s get started and explore some incredible things you can do in Excel with Text to Columns.<\/p>\n<ul>\n<li>\n<h3><strong id=\"example-1\u2013-split-first-and-last-names-in-names\">Example 1 \u2013 Split first and last names in names<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>With Excel&#8217;s Text to Columns feature, you can easily divide text values into different cells in a row.<\/p>\n<p>Consider the following dataset as an example. Suppose you want to split the first and last names and obtain them in different cells.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-4259 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-2.png\" alt=\"Split Text to Column in Excel\" width=\"329\" height=\"176\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-2.png 329w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-2-300x160.png 300w\" sizes=\"(max-width: 329px) 100vw, 329px\" \/><\/p>\n<p><strong>The procedures for utilizing Text to Columns in Excel to separate the first and last names are described below:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> Choose every name in the column (A2:A6 in this example)<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-4260 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-3.png\" alt=\"Split Text to Column in Excel\" width=\"376\" height=\"163\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-3.png 376w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-3-300x130.png 300w\" sizes=\"(max-width: 376px) 100vw, 376px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Toggle to the &#8220;Data&#8221; tab. Select the &#8220;Text to Columns&#8221; option under the &#8220;Data Tools&#8221; group.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4261\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-4.png\" alt=\"Multiple ways to split text to column\" width=\"569\" height=\"112\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-4.png 569w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-4-300x59.png 300w\" sizes=\"(max-width: 569px) 100vw, 569px\" \/><\/p>\n<p><strong>Step 3:<\/strong> The &#8220;Convert Text to Column Wizard&#8221; dialog box will appear. Click \u201cNext\u201d after choosing \u201cDelimited\u201d (which enables you to use a space as the separator).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4262\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-5.png\" alt=\"Multiple ways to split text to column\" width=\"359\" height=\"292\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-5.png 359w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-5-300x244.png 300w\" sizes=\"(max-width: 359px) 100vw, 359px\" \/><\/p>\n<p><strong>Step 4:<\/strong> Click \u201cNext\u201d after selecting the \u201cSpace\u201d option.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4263\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-6.png\" alt=\"Multiple ways to split text to column \" width=\"370\" height=\"305\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-6.png 370w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-6-300x247.png 300w\" sizes=\"(max-width: 370px) 100vw, 370px\" \/><\/p>\n<p><strong>Step 5:<\/strong> Make B2 the final destination cell (else it will overwrite the existing data). Select \u201cFinish\u201d.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4264\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-7.png\" alt=\"Multiple ways to split text to column\" width=\"336\" height=\"282\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-7.png 336w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-7-300x252.png 300w\" sizes=\"(max-width: 336px) 100vw, 336px\" \/><\/p>\n<p>Following the procedures above would immediately separate the names into first and last names (with first names in column B and last name in column C).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4265\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-8.png\" alt=\"Multiple ways to split text to column\" width=\"349\" height=\"185\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-8.png 349w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-8-300x159.png 300w\" sizes=\"(max-width: 349px) 100vw, 349px\" \/><\/p>\n<p><strong>Note:<\/strong><\/p>\n<ul>\n<li>When your name solely consists of your first and last names, this strategy works effectively. This might not work if there are initials or middle names.<\/li>\n<li>When you use the Text to Columns tool, you receive a static output. This implies that in order to obtain updated results in the event that the original data changes, you will need to repeat the procedure.<\/li>\n<li>\n<h3><strong id=\"example2\u2013split-email-addresses-by-username-and-domain-name\">Example 2 \u2013 Split email addresses by username and domain name<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>You can select your own delimiter to split text using Text to Columns.<\/p>\n<p>Since usernames and domain names are separated by the @ symbol, this can be used to divide email addresses into usernames and domain names.<\/p>\n<p>Assume you have the dataset as follows:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4266\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-9.png\" alt=\"Multiple ways to split text to column\" width=\"156\" height=\"164\" \/><\/p>\n<p><strong>Here are the steps for using the Text to Columns function to separate these usernames and domain names.<\/strong><\/p>\n<p><strong>Step 1:<\/strong> Choose the whole\u00a0data set except the header.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4267\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-10.png\" alt=\"Multiple ways to split text to column\" width=\"363\" height=\"164\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-10.png 363w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-10-300x136.png 300w\" sizes=\"(max-width: 363px) 100vw, 363px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Toggle to the &#8220;Data&#8221; tab. Select the &#8220;Text to Columns&#8221; option under the &#8220;Data Tools&#8221; group.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4268\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-11.png\" alt=\"Multiple ways to split text to column\" width=\"572\" height=\"117\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-11.png 572w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-11-300x61.png 300w\" sizes=\"(max-width: 572px) 100vw, 572px\" \/><\/p>\n<p><strong>Step 3:<\/strong> Ensure that \u201cDelimited\u201d is chosen (which is also the default selection). Select Next.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4269\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-12.png\" alt=\"Multiple ways to split text to column\" width=\"414\" height=\"337\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-12.png 414w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-12-300x244.png 300w\" sizes=\"(max-width: 414px) 100vw, 414px\" \/><\/p>\n<p><strong>Step 4:<\/strong> In the box to the right of &#8220;Other,&#8221; type @. Make sure to uncheck every alternative (if checked). Select Next.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4270\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-13.png\" alt=\"Multiple ways to split text to column\" width=\"422\" height=\"344\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-13.png 422w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-13-300x245.png 300w\" sizes=\"(max-width: 422px) 100vw, 422px\" \/><\/p>\n<p><strong>Step 5:<\/strong> The cell where you want the outcome should be the new destination cell. Select Finish.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4271\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-14.png\" alt=\"Multiple ways to split text to column\" width=\"399\" height=\"324\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-14.png 399w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-14-300x244.png 300w\" sizes=\"(max-width: 399px) 100vw, 399px\" \/><\/p>\n<p>The first name and last name would be provided to you in different cells along with a split email address.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4272\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-15.png\" alt=\"Multiple ways to split text to column\" width=\"354\" height=\"160\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-15.png 354w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-15-300x136.png 300w\" sizes=\"(max-width: 354px) 100vw, 354px\" \/><\/p>\n<ul>\n<li>\n<h3><strong id=\"example-3\u2013from-the-url,-obtain-the-root-domain\">Example 3 \u2013 from the URL, obtain the Root Domain<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>If you work with online URLs, you might occasionally need to know how many different root domains there are.<\/p>\n<p>For instance, the root domain, www.google.com, is the same for both http:\/\/www.google.com\/example1 and http:\/\/google.com\/example2.<\/p>\n<p>Assume you have the dataset as follows:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4273\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-16.png\" alt=\"Multiple ways to split text to column\" width=\"275\" height=\"237\" \/><\/p>\n<p><strong>The steps to extract the root domain from these URLs are as follows:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> Choose the whole\u00a0data set except the header.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4274\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-17.png\" alt=\"Multiple ways to split text to column\" width=\"507\" height=\"265\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-17.png 507w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-17-300x157.png 300w\" sizes=\"(max-width: 507px) 100vw, 507px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Toggle to the &#8220;Data&#8221; tab. Select the &#8220;Text to Columns&#8221; option under the &#8220;Data Tools&#8221; group.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4275\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-18.png\" alt=\"Multiple ways to split text to column\" width=\"569\" height=\"115\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-18.png 569w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-18-300x61.png 300w\" sizes=\"(max-width: 569px) 100vw, 569px\" \/><\/p>\n<p><strong>Step 3:<\/strong> Ensure that \u201cDelimited\u201d is chosen (which is also the default selection). Select Next<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4276\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-19.png\" alt=\"Multiple ways to split text to column\" width=\"494\" height=\"395\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-19.png 494w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-19-300x240.png 300w\" sizes=\"(max-width: 494px) 100vw, 494px\" \/><\/p>\n<p><strong>Step 4:<\/strong> Choose Other, then type &#8220;\/&#8221; (a forward slash) in the box next to it. Make sure to uncheck every alternative (if checked). Select Next.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4277\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-20.png\" alt=\"Multiple ways to split text to column\" width=\"432\" height=\"355\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-20.png 432w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-20-300x247.png 300w\" sizes=\"(max-width: 432px) 100vw, 432px\" \/><\/p>\n<p><strong>Step 5:<\/strong> The cell where you want the outcome should be the new destination cell. Select Finish.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4278\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-21.png\" alt=\"Multiple ways to split text to column\" width=\"432\" height=\"352\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-21.png 432w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-21-300x244.png 300w\" sizes=\"(max-width: 432px) 100vw, 432px\" \/><\/p>\n<p>By doing this, you may break up the URL and get the root domain (in the third column as there were two forward slashes before it).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4279\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-22.png\" alt=\"Multiple ways to split text to column\" width=\"579\" height=\"249\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-22.png 579w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-22-300x129.png 300w\" sizes=\"(max-width: 579px) 100vw, 579px\" \/><\/p>\n<p>Now, just eliminate the duplicates to determine the number of unique domains.<\/p>\n<p><strong>Note:<\/strong> When all of your URLs start with http:\/\/, this will function properly. If not, the root domain will be displayed in the first column by itself. Making these URLs consistent before using Text to Columns is a smart idea.<\/p>\n<ul>\n<li>\n<h3><strong id=\"example-4\u2013transform-unreliable-date-formats-into-reliable-date-formats\">Example 4 \u2013 Transform Unreliable Date Formats Into Reliable Date Formats<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>There is a chance that the date format will be inaccurate if you import your data from a text file or obtain it from databases like SAP\/Oracle\/Capital IQ.<\/p>\n<p>Excel only supports a small number of file types; any other file format must be converted before it can be used in Excel.<\/p>\n<p>Assume that your dates are in the following format (which are not in the valid Excel date format).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4280\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-23.png\" alt=\"Multiple ways to split text to column\" width=\"226\" height=\"205\" \/><\/p>\n<p><strong>The steps to change these into acceptable date formats are as follows:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> Choose the whole\u00a0data set except header.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4281\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-24.png\" alt=\"Multiple ways to split text to column\" width=\"379\" height=\"200\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-24.png 379w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-24-300x158.png 300w\" sizes=\"(max-width: 379px) 100vw, 379px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Toggle to the &#8220;Data&#8221; tab. Select the &#8220;Text to Columns&#8221; option under the &#8220;Data Tools&#8221; group.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4282\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-25.png\" alt=\"Multiple ways to split text to column\" width=\"571\" height=\"116\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-25.png 571w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-25-300x61.png 300w\" sizes=\"(max-width: 571px) 100vw, 571px\" \/><\/p>\n<p><strong>Step 3:<\/strong> Ensure that \u201cDelimited\u201d is chosen (which is also the default selection). Select Next<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4283\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-26.png\" alt=\"Multiple ways to split text to column\" width=\"443\" height=\"362\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-26.png 443w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-26-300x245.png 300w\" sizes=\"(max-width: 443px) 100vw, 443px\" \/><\/p>\n<p><strong>Step 4:<\/strong> Ensure that the NO delimiter option is chosen. Select Next.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4284\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-27.png\" alt=\"Multiple ways to split text to column\" width=\"432\" height=\"351\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-27.png 432w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-27-300x244.png 300w\" sizes=\"(max-width: 432px) 100vw, 432px\" \/><\/p>\n<p><strong>Step 5:<\/strong> Choose Date in the Column Data Format and then the desired format (DMY would mean date month and year). Click on Finish.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4285\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-28.png\" alt=\"Multiple ways to split text to column\" width=\"429\" height=\"349\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-28.png 429w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-28-300x244.png 300w\" sizes=\"(max-width: 429px) 100vw, 429px\" \/><\/p>\n<p>These erroneous date formats would be instantaneously converted into legitimate date forms that you may use in Excel.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4286\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-29.png\" alt=\"Multiple ways to split text to column\" width=\"248\" height=\"181\" \/><\/p>\n<ul>\n<li>\n<h3><strong id=\"example-5\u2013convert-text-to-numbers\">Example 5 \u2013 Convert Text to Numbers<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>The numbers are occasionally transformed into text representation when you import data from databases or other file formats.<\/p>\n<p>There are various methods for this to occur:<\/p>\n<ul>\n<li>The number comes before an apostrophe. As a result, the number is handled as text.<\/li>\n<li>Using text functions like LEFT, RIGHT, or MID to generate numbers<\/li>\n<\/ul>\n<p>The issue with this is that Excel functions like SUM and AVERAGE ignore these numbers (which are in text format).<\/p>\n<p>Consider the following dataset, where the numbers are presented in text format (note that these are aligned to the left).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4287\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-30.png\" alt=\"Multiple ways to split text to column\" width=\"177\" height=\"132\" \/><\/p>\n<p><strong>To convert text to numbers using Text to Columns, follow these steps:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> Choose the whole\u00a0data set except header.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4288\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-31.png\" alt=\"Multiple ways to split text to column\" width=\"396\" height=\"144\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-31.png 396w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-31-300x109.png 300w\" sizes=\"(max-width: 396px) 100vw, 396px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Toggle to the &#8220;Data&#8221; tab. Select the &#8220;Text to Columns&#8221; option under the &#8220;Data Tools&#8221; group.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4290\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-32.png\" alt=\"Multiple ways to split text to column\" width=\"571\" height=\"117\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-32.png 571w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-32-300x61.png 300w\" sizes=\"(max-width: 571px) 100vw, 571px\" \/><\/p>\n<p><strong>Step 3:<\/strong> Ensure that \u201cDelimited\u201d is chosen (which is also the default selection). Select Next<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4291\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-33.png\" alt=\"Multiple ways to split text to column\" width=\"437\" height=\"357\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-33.png 437w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-33-300x245.png 300w\" sizes=\"(max-width: 437px) 100vw, 437px\" \/><\/p>\n<p><strong>Step 4:<\/strong> Ensure that the NO delimiter option is chosen. Select Next.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4292\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-34.png\" alt=\"Multiple ways to split text to column\" width=\"400\" height=\"327\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-34.png 400w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-34-300x245.png 300w\" sizes=\"(max-width: 400px) 100vw, 400px\" \/><\/p>\n<p><strong>Step 5:<\/strong> Select \u201cGeneral\u201d from the Column data format menu. Change the destination cell as well to the one where you want the outcome to appear. Select Finish.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4293\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-35.png\" alt=\"Multiple ways to split text to column\" width=\"415\" height=\"343\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-35.png 415w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-35-300x248.png 300w\" sizes=\"(max-width: 415px) 100vw, 415px\" \/><\/p>\n<p>By doing this, these numbers would once again be in General format and be used in formulas.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4294\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-36.png\" alt=\"Multiple ways to split text to column\" width=\"454\" height=\"164\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-36.png 454w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-36-300x108.png 300w\" sizes=\"(max-width: 454px) 100vw, 454px\" \/><\/p>\n<ul>\n<li>\n<h3><strong id=\"example-6\u2013-extract-first-five-characters-of-a-string\">Example 6 \u2013 Extract the First five Characters of a String<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>It&#8217;s occasionally necessary to remove the first few characters from a string. The first five characters (or any other number of characters) in transactional data that serve as a unique identifier may be the case in these situations.<\/p>\n<p>For instance, the first five letters in the data set below are specific to a product line.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4295\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-37.png\" alt=\"Multiple ways to split text to column\" width=\"227\" height=\"266\" \/><\/p>\n<p><strong>Using Text to Columns, use these steps to quickly extract the first five characters from this data:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> Choose the whole\u00a0data set except header.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4296\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-38.png\" alt=\"Multiple ways to split text to column\" width=\"310\" height=\"271\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-38.png 310w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-38-300x262.png 300w\" sizes=\"(max-width: 310px) 100vw, 310px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Toggle to the &#8220;Data&#8221; tab. Select the &#8220;Text to Columns&#8221; option under the &#8220;Data Tools&#8221; group.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4297\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-39.png\" alt=\"Multiple ways to split text to column\" width=\"568\" height=\"115\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-39.png 568w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-39-300x61.png 300w\" sizes=\"(max-width: 568px) 100vw, 568px\" \/><\/p>\n<p><strong>Step 3:<\/strong> make certain \u201cFixed Width\u201d is chosen. Select Next.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4298\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-40.png\" alt=\"Multiple ways to split text to column\" width=\"430\" height=\"352\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-40.png 430w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-40-300x246.png 300w\" sizes=\"(max-width: 430px) 100vw, 430px\" \/><\/p>\n<p><strong>Step 4:<\/strong> Drag the vertical line to the fifth character in the text after which it will be placed in the Data preview area. Select Next.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4299\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-41.png\" alt=\"Multiple ways to split text to column\" width=\"434\" height=\"356\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-41.png 434w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-41-300x246.png 300w\" sizes=\"(max-width: 434px) 100vw, 434px\" \/><\/p>\n<p><strong>Step 5:<\/strong> The cell where you want the outcome should be the new destination cell. Select Finish.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4300\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-42.png\" alt=\"Multiple ways to split text to column\" width=\"440\" height=\"358\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-42.png 440w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-42-300x244.png 300w\" sizes=\"(max-width: 440px) 100vw, 440px\" \/><\/p>\n<p>By doing this, your data set would be divided, giving you the first five characters of each transaction id in one column and the remaining characters in the second.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4301\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-43.png\" alt=\"Multiple ways to split text to column\" width=\"313\" height=\"260\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-43.png 313w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-43-300x249.png 300w\" sizes=\"(max-width: 313px) 100vw, 313px\" \/><\/p>\n<p><strong>Note:<\/strong> To divide the data into more than two columns, you can also set up additional vertical lines. To set the divider, simply click and drag the mouse anywhere within the Data Preview window.<\/p>\n<ul>\n<li>\n<h3><strong id=\"example-7\u2013negative-integers-from-numbers-with-a-trailing-minus-sign\">Example 7 \u2013 Negative integers from numbers with a trailing minus sign<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>Even while you might not run into this very often, you could occasionally need to make the numbers negative by adding the following minus signs.<\/p>\n<p>The best solution for this is Text to Columns.<\/p>\n<p>Assume you have the dataset as follows:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4302\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-44.png\" alt=\"Multiple ways to split text to column\" width=\"288\" height=\"173\" \/><\/p>\n<p><strong>These trailing minuses can be turned into negative numbers by following these steps:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> Choose the whole\u00a0data set except header.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4303\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-45.png\" alt=\"Multiple ways to split text to column\" width=\"464\" height=\"169\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-45.png 464w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-45-300x109.png 300w\" sizes=\"(max-width: 464px) 100vw, 464px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Toggle to the &#8220;Data&#8221; tab. Select the &#8220;Text to Columns&#8221; option under the &#8220;Data Tools&#8221; group.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4304\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-46.png\" alt=\"Multiple ways to split text to column\" width=\"569\" height=\"113\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-46.png 569w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-46-300x60.png 300w\" sizes=\"(max-width: 569px) 100vw, 569px\" \/><\/p>\n<p><strong>Step 3:<\/strong> Ensure that \u201cDelimited\u201d is chosen (which is also the default selection). Select Next<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4305\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-47.png\" alt=\"Multiple ways to split text to column\" width=\"435\" height=\"352\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-47.png 435w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-47-300x243.png 300w\" sizes=\"(max-width: 435px) 100vw, 435px\" \/><\/p>\n<p><strong>Step 4:<\/strong> Ensure that the NO delimiter option is chosen. Select Next.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4306\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-48.png\" alt=\"Multiple ways to split text to column\" width=\"440\" height=\"357\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-48.png 440w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-48-300x243.png 300w\" sizes=\"(max-width: 440px) 100vw, 440px\" \/><\/p>\n<p><strong>Step 4:<\/strong> Select \u201cAdvanced\u201d from the menu.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4307\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-49.png\" alt=\"Multiple ways to split text to column\" width=\"465\" height=\"372\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-49.png 465w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-49-300x240.png 300w\" sizes=\"(max-width: 465px) 100vw, 465px\" \/><\/p>\n<p><strong>Step 5:<\/strong> Pick the &#8220;Trailing minus for negative numbers&#8221; option in the Advanced Text Import Settings dialog box. Select OK.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4308\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-50.png\" alt=\"Multiple ways to split text to column\" width=\"324\" height=\"215\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-50.png 324w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-50-300x199.png 300w\" sizes=\"(max-width: 324px) 100vw, 324px\" \/><\/p>\n<p><strong>Step 6:<\/strong> Decide on the final cell. Select Finish.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4309\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-51.png\" alt=\"Multiple ways to split text to column\" width=\"460\" height=\"377\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-51.png 460w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-51-300x246.png 300w\" sizes=\"(max-width: 460px) 100vw, 460px\" \/><\/p>\n<p>This would immediately move the negative sign from the beginning to the end of the number. These figures are now simple to utilize in computations and formulas.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4310\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Multiple-ways-to-split-text-to-column-52.png\" alt=\"Multiple ways to split text to column\" width=\"289\" height=\"173\" \/><\/p>\n<h2><strong>Application of Split Text to Column in Excel<\/strong><\/h2>\n<div class=\"w-full text-token-text-primary\" data-testid=\"conversation-turn-372\">\n<div class=\"px-4 py-2 justify-center text-base md:gap-6 m-auto\">\n<div class=\"flex flex-1 text-base mx-auto gap-3 md:px-5 lg:px-1 xl:px-5 md:max-w-3xl lg:max-w-[40rem] xl:max-w-[48rem] group\">\n<div class=\"relative flex w-full flex-col agent-turn\">\n<div class=\"flex-col gap-1 md:gap-3\">\n<div class=\"flex flex-grow flex-col max-w-full\">\n<div class=\"min-h-[20px] text-message flex flex-col items-start gap-3 whitespace-pre-wrap break-words [.text-message+&amp;]:mt-5 overflow-x-auto\" data-message-author-role=\"assistant\" data-message-id=\"fcfb91b1-6d79-4424-9746-a54b2680fa01\">\n<div class=\"markdown prose w-full break-words dark:prose-invert light\">\n<ul>\n<li><strong>Separating Names<\/strong>: Divide full names into separate first and last name columns for better data organization and personalization in communications.<\/li>\n<li><strong>Extracting Contact Information<\/strong>: Split a single column of combined address, email, or phone information into separate columns for each data type, enhancing database management.<\/li>\n<li><strong>Data Reformatting<\/strong>: Convert text from a single column, such as a list of dates or numbers mixed with text, into structured, usable formats across multiple columns.<\/li>\n<li><strong>Importing Data Cleaning<\/strong>: Clean up and reorganize data imported from different sources like CSV files, by splitting text into columns based on delimiters such as commas or tabs.<\/li>\n<li><strong>Product Information Organization<\/strong>: Separate product codes, descriptions, and specifications listed in a single column into individual columns for each attribute, improving inventory management.<\/li>\n<li><strong>Financial Data Analysis<\/strong>: Break down financial statements or reports into more detailed and analyzable components, like splitting a column of transactions into date, amount, and description columns.<\/li>\n<\/ul>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"mt-1 flex justify-start gap-3 empty:hidden\"><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<p>For ready-to-use Dashboard Templates:<\/p>\n<ol>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Finance\" target=\"_blank\" rel=\"noopener\">Financial Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Sales\" target=\"_blank\" rel=\"noopener\">Sales Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Human-Resource\" target=\"_blank\" rel=\"noopener\">HR Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.executiveknowledge.org\/dashboard-templates\/35\" target=\"_blank\" rel=\"noopener\">Data Visualization Charts<\/a><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>Split Text to Column in Excel is a crucial feature for anyone looking to enhance their data organization and analysis. By mastering this tool, you can transform cumbersome, mixed data into structured, easy-to-analyze formats, streamlining your workflows and uncovering insights more quickly. Whether you&#8217;re reformatting imported data, segregating names and addresses, or preparing reports, the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[526],"tags":[528,527],"class_list":["post-4257","post","type-post","status-publish","format-standard","hentry","category-text-to-column","tag-text-to-column","tag-text-to-column-in-excel"],"_links":{"self":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/4257","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/comments?post=4257"}],"version-history":[{"count":5,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/4257\/revisions"}],"predecessor-version":[{"id":8857,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/4257\/revisions\/8857"}],"wp:attachment":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/media?parent=4257"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/categories?post=4257"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/tags?post=4257"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}