{"id":4180,"date":"2018-12-26T17:29:27","date_gmt":"2018-12-26T22:29:27","guid":{"rendered":"http:\/\/lisa.rushworth.us\/?p=4180"},"modified":"2018-12-26T17:30:08","modified_gmt":"2018-12-26T22:30:08","slug":"did-you-know-there-are-ways-to-split-data-in-excel","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=4180","title":{"rendered":"Did you know \u2026 there are ways to split data in Excel?"},"content":{"rendered":"\n

Applications can generate data in formats that aren\u2019t quite<\/em> useful \u2013 glomming multiple fields\ntogether to make something unusable. And asking people to type information can\nyield inconsistent results \u2013 is my name Lisa Rushworth, Lisa J Rushworth, or\njust Lisa? Excel has several functions that allow you to produce consistent,\nusable data (without copy\/pasting or deleting things!)<\/p>\n\n\n\n

Flash Fill<\/em><\/strong><\/p>\n\n\n\n

Flash\nFill<\/a> will try to figure it out for you. Add an empty column (or more) and manually\ntype one or two values. On the \u201cData\u201d ribbon bar, select \u201cFlash Fill\u201d and Excel\nwill use the data you\u2019ve entered into the row to figure out what should go in\nthe rest of the row. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The guesses aren\u2019t 100% accurate \u2013 especially if your\ninformation is not consistent \u2013 but it\u2019s a lot<\/em>\neasier to delete the handful of things that are obviously<\/em> not zip codes \u2026<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Than to work out a formula that extracts the same\ninformation<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Text to columns<\/em><\/strong><\/p>\n\n\n\n

Text to columns uses the fixed-length file and delimited\nfile import wizard on a column of data \u2013 essentially treating that column as a\nfile to be imported. In this example, a DateTime value is provided in a way\nthat Excel only sees it as a string. And, frankly, I am not interested on the\nexact hundredth of a second the event occurred. What I really<\/em> want to do is group these creation dates by day, so all I\nneed is the date component.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If you want to retain all the data, you\u2019ll need to insert empty\ncolumns to the right \u2013 otherwise the data being split out can overwrite<\/em> existing data. In my case, I only<\/em> want to keep one of the new columns.\n<\/p>\n\n\n\n

Highlight the column that holds your data. On the \u201cData\u201d\nribbon, select \u201cText to columns\u201d<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Select if the column should be split based on a fixed width\ndefinition or a delimiter and click \u2018Next\u2019<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Indicate the proper delimiter \u2013 in this case, I need to use \u2018Other\u2019\nand enter the letter T. A preview of the split data will appear below \u2013 make sure\nit looks reasonable. Click \u201cNext\u201d.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

For each new row, you can<\/em>\nspecify a data type. Or leave the type set to \u201cGeneral\u201d and Excel will try to\nfigure it out. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If you do not need to retain the data, select \u201cDo not import\nthis column (skip)\u201d. Click \u201cFinish\u201d to split your column.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Voil\u00e0 \u2013 I\u2019ve got a usable date value.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Notice, though, I have lost my original data. If you want to\nretain<\/em> the original data, create a\ncopy of the column. In this example, I want to know how many e-mail addresses\nuse each domain, but I want to have the e-mail addresses in a recognizable and\nusable format too. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Text to columns will still replace<\/em> the values from the selected column. But the copy will\ncontain the original text.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

You can even use Text to columns to sort out odd data that doesn\u2019t<\/em> actually get split into multiple\ncolumns. In this example, negative values have the minus sign after the number \u2026\nwhich isn\u2019t actually<\/em> a negative\nnumber and isn\u2019t usable in calculations.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Pick a delimiter that doesn\u2019t appear in your data, and you\u2019ll\nonly have one column. When selecting the data format, click \u201cAdvanced\u201d<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Make sure the \u201cTrailing minus for negative numbers\u201d checkbox\nis checked and click OK. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

And we\u2019ve got negative\nnumbers<\/em><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Right, Left, Mid, and Search Functions:<\/em><\/strong><\/p>\n\n\n\n

You can also use the Search<\/a>\nfunction in conjunction with Right<\/a>,\nLeft<\/a>,\nand Mid<\/a>\nto extract components of column data. In this example, we have first and last\nnames. Since there are a few middle initials in there, we cannot just split on\nthe space character. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

These formulae aren\u2019t perfect \u2013 Mary Ann will have \u2018Mary\u2019 as\na first name \u2013 but <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Working out where to start the text extraction and the number\nof characters to extract can get complex. I\u2019ll usually include the Substitute<\/a>\nfunction to simplify things a little \u2013 the zip code, in this case, is whatever\nis left over after we find the city and state. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Producing columns with the city, state, and zip code from\nthe \u2018Location\u2019 column. <\/p>\n\n\n\n

\"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"

Applications can generate data in formats that aren\u2019t quite useful \u2013 glomming multiple fields together to make something unusable. And asking people to type information can yield inconsistent results \u2013 is my name Lisa Rushworth, Lisa J Rushworth, or just Lisa? Excel has several functions that allow you to produce consistent, usable data (without copy\/pasting …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[677],"tags":[675,708,709,691,729,731],"class_list":["post-4180","post","type-post","status-publish","format-standard","hentry","category-office-365","tag-did-you-know","tag-excel","tag-microsoft-excel","tag-microsoft-office","tag-microsoft-office-365","tag-ms-excel"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4180","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4180"}],"version-history":[{"count":1,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4180\/revisions"}],"predecessor-version":[{"id":4201,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4180\/revisions\/4201"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4180"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4180"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4180"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}