{"id":662,"date":"2018-01-15T09:56:52","date_gmt":"2018-01-15T15:56:52","guid":{"rendered":"http:\/\/www.iqaccountingsolutions.com\/blog\/?p=662"},"modified":"2018-03-29T10:55:26","modified_gmt":"2018-03-29T15:55:26","slug":"how-to-split-one-column-into-multiple-columns-in-excel","status":"publish","type":"post","link":"https:\/\/www.iqaccountingsolutions.com\/blog\/how-to-split-one-column-into-multiple-columns-in-excel\/","title":{"rendered":"How to Split One Column into Multiple Columns In Excel"},"content":{"rendered":"

Many times on a project you have the right data, but in the wrong format. I\u2019ve talked before about how you can use Excel to combine multiple columns<\/span><\/a> of data into one. Today I\u2019ll show you how you break apart a single column of data into two or more columns using Text To Columns.<\/span><\/p>\n

Let\u2019s say you have an address list in Excel and there is one column for the name with the names formatted as last name, first name<\/span><\/em>. But you want first name and last name in separate columns. Select the column that the names are in, click on the Data<\/span><\/em> tab on the ribbon, and click the Text To Columns<\/span><\/em> button.<\/span><\/p>\n

The Convert Text to Columns Wizard<\/span><\/em> will open. The first step asks if your data is delimited or fixed width. Delimited means there is a specific character, such as a comma, tab, or space that separates each piece of information. Fixed Width means that a certain number of characters is allotted to each piece of information. Since we know that there will be a space separating the first name and last name, choose Delimited<\/span><\/em> and click Next<\/span><\/em>.<\/span><\/p>\n

In the next window, uncheck Tab<\/span><\/em> and check Space<\/span><\/em> in the list of delimiters (field separators). This tells Excel that anything separated by spaces should be split into different columns.\u00a0 You will see vertical lines appear in the preview of your data to show where your data will be broken into columns. If you had chosen fixed width<\/span><\/em> instead of delimited<\/span><\/em> you could click in the preview of your data to insert column breaks.\u00a0 The text qualifier<\/span><\/em> allows you to select a character that tells Excel to ignore any occurrences of your delimiter character that fall between two text qualifiers. In other words, if your delimiter is a space and your text qualifier is a quotation mark, Excel knows that any spaces that fall between two quotation marks are part of your data, and do not indicate a new column break.\u00a0 For example Ludwig van Beethoven<\/span><\/em> would get split into 3 columns, but Ludwig \u201cvan Beethoven\u201d<\/span><\/em> would only be split into 2 columns because the space between the quotation marks would not be considered a delimiter.\u00a0 Click Next<\/span><\/em>.<\/span><\/p>\n

In the third step you can format each column as General, Text, Date, or tell Excel to skip that column.\u00a0 In most cases you can leave it on General and Excel can figure out what is text and what is a number or date.\u00a0 However if you your data has numbers with leading zeros, such as zip codes, be sure to set that column as Text<\/span><\/em>.\u00a0 Since numbers can\u2019t have zero as their first digit, Excel will remove any leading zeros from numbers if you leave the format set to General.<\/span><\/p>\n

If you want the new data to be located somewhere other than the original column and the columns to its right, you can specify the new location in the Destination<\/span><\/em> field.\u00a0 Click Finish<\/span><\/em> to return to the worksheet and see your new data.<\/span><\/p>\n

When Excel splits your data into columns, it does not insert new columns to hold that data.\u00a0 If anything is in the cells to the right, it will be overwritten by the data from the cells you are splitting. So It\u2019s always a good idea to insert a few extra columns before using Text To Columns<\/span><\/em>.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Many times on a project you have the right data, but in the wrong format. In this tip I’ll show you how you can break apart a single column of data into two or more columns in Excel using the Text To Columns feature.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[9,5],"tags":[],"_links":{"self":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/662"}],"collection":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/comments?post=662"}],"version-history":[{"count":3,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/662\/revisions"}],"predecessor-version":[{"id":1130,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/662\/revisions\/1130"}],"wp:attachment":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/media?parent=662"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/categories?post=662"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/tags?post=662"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}