If you’re an Excel user, chances are that at some point in your life, you have come across a spreadsheet stacked full of data that is no good to anyone! If you’ve been following my recent articles, you’ll be aware that I’m a huge advocate of consistent and tidy data sets that are functional and useful. In order to achieve this, you need to think a little when designing your spreadsheet and break the data down into suitable sections.
This is all well and good if you are creating your spread sheet from scratch. But what happens when you have inherited a monster of a spreadsheet that is so useless you can’t even sort it correctly because the first and last names have been put into one cell together?! The answer is, of course, you could edit the data manually, add in some columns, split out some data and go through all 10,000 records putting the last name into the cell next to the first name so that you can sort your sheet alphabetically… if you’ve really got nothing better to do! But for those of us who value our precious time and can’t afford to spend hours sifting through mountains of data there is exactly a tool for the job that will have this task completed in 2 minutes.
It is quite cleverly named ‘Text to Columns’ and it really does what it says on the tin! It works best with data that is fairly consistent and not too complicated (such as a name which will usually have 2 words with a space in between) and you do need to make sure that you have enough blank columns for the data once it has been split otherwise it will overwrite the contents of the other cells.
The first thing you’ll need to do is to highlight the data that needs to be separated. Now select the ‘Data’ tab and under the ‘Data Tools’ group select ‘Text to Columns’.
You will then be presented with the box below, and a wizard to guide you through the process. The delimited option is likely to already be checked by default. Check that this is the case then click ‘Next’.
Next you can choose the criteria (delimiters) for breaking your data down. In simple terms, this is the thing that will indicate to Excel the points at which it should split the data. In this case we are going to check the ‘Space’ option but if you have different types of data to split, you could also use a comma, semi colon or define a delimiter that is specific to the data you are using. This is where you do need some consistency in your data, for example a space between two words, so that Excel can determine how to go about this job . You can also choose to treat consecutive delimiters as one. In most cases you should leave this box checked to avoid your data being split into three columns where there are two spaces between two words. You will see a preview of your data in the box at the bottom so you can be sure that you have picked the right options. Click ‘Next’
The next screen allows you to format your data and decide where to put it. Settings are set to general by default so you can skip this section if you want to, but you also might want to make some general formatting changes now. Ensure that the ‘destination’ box returns the correct cell for where you want your newly split data to be put. When you’re happy, click ‘Finish’.
E Voila! Your useless and untidy column of jumbled names is now transformed into two neat and tidy columns of first names and surnames, ready to sort, filter, or just look at in amazement, and all in a fraction of the time it was going to take you!
Steph Middleton is an experienced Virtual Assistant and MD of Outhouse UK