Many of us spend a lot of time working in Excel, and on many occasions could take advantage of some helpful tips and tricks to work more productively and with greater ease. Data splitting is an example of this.
Let’s take a look at two different methods used to break up information from within a list.
Sometimes you will find that there is too much data contained within a single cell, and it would be more serviceable to split that data into separate cells. For example, a cell containing first and last names, such as John Smith, might be preferable if split into two cells, one for the first name, one for the surname.
Regardless of the version of excel you are using, this can be done by using the following steps:
Under the data tab, create a destination area that you will move the split information into.
Highlight all of the information you wish to split, and under the data tab is where you select your splitting point. There are two ways to establish a splitting point, a fixed point splits according to a specific number of characters, whereas a limited point allows you to pick a characteristic such as the space between the first and last name, and split the information from there.
Once the splitting point has been established the information will be sorted accordingly into the two columns.
Excel 2013 has a feature called Flash Fill that will do a lot of the above work for you. When using flash fill all you have to do is enter the name or other value you want to split into an empty column at the end of your data. Once you move on to the second row, Excel will recognize what you are doing and the information will be auto-filled for you. Simply hit enter and your information will be split. If the information is not being split as you want it you can press escape and the auto-generated information will be removed.