Data Manipulation

Working with Lists

Text to Columns or Splitting a List

  Sometimes, you have a list where each item in the list has more information than you want.  For example, you have a list of names in the form of “Smith, John” and you want, say, “John Smith”. I think this is the most common issue people ask me for help with.  Folks who never otherwise use Excel will use it and have this problem.  Fortunately, it’s really easy to fix. On the Data Tab (or in the “Data” Group of the XL4Accountants Tab) there is an icon called “Text to Columns”.  This function will take a single column of text and apply a process to split the string in each cell based on the guidelines you provide. First, some terms.  As you work with Text to Columns, you will be asked if your data has “delimiters”.  A delimiter is any character that serves to define a “break” between two or more logical portions of your string.  In our example, above, “Smith, John” uses the “,” (comma) as a delimiter, as it’s only purpose is to inform the reader that Smith and John are two distinct words. OK, let’s walk through an example. To the right we have a typical list.  6 names, last name first.  We want to change it to first name first, and get rid of the comma. First, we highlight the list, then choose the “Text to Columns” function, The following popup is presented:   The 2nd of the 3 steps: And, the third and final step:
We have commas separating the fields in our list, so we’ll choose “delimited” Good!  Now, click “Next” Again, we have commas as delimiters, so we choose commas.  Note that ANY character can be a delimiter - for example, in “John Smith”, the “ “ (spacebar) is a delimiter. Sometimes your delimiter repeats.  You may have a typo and get 2 commas, one right after the other.  In this case, tick this box to make BOTH the commas disappear. What if you WANT to keep a comma? (or other delimiter)?  Put this character (you can choose which one) IMMEDIATELY before the delimiting character, and Excel will treat that character AS a character, not a delimiter. Since we told Excel to treat the comma as a delimiter, it has removed the commas and replaced them (visually only) with a bar separating our first and last names. General almost always works.  I don’t think I’ve ever chosen anything else. By default, the first column of your new data set will replace your existing column, and the new columns (one, in this case) will fall to the right.  You can change that with this option. Click this icon to go to the worksheet so you can select a cell (if you want to...).
And that’s it!  Click “Finish” and you now have two separate fields - one for last name, and one for first name. If that’s all you wanted to do, you’re done.  However, we did want to end up with ONE column, first name first.  To do that, go to the next page and we’ll look at CONCATENATION.
Home Home Why subscribe? Why subscribe? Training Training Purchase Purchase My Account My Account Blog Blog