Excel provides a feature that is intended to split a single column of data into multiple columns. For instance, if you had a column of full names (first and last) and needed to separate these out into two columns:
Text to Columns
'Text to columns' is found in the 'data' tab of the ribbon:
To use the feature you must first select the column of data you wish to separate:
once selected you can press the button, this will open the 'wizard' which will ask you a few questions in order to help separate the data:
There are two main options: Delimited and Fixed Width, which I will now explain below:
Delimited
Using this method we can use certain characters to denote the separation between two character strings (in this case, names). For example, if you have a space between the forename and surname, we can use this to tell Excel to separate the column at this point. Excel will also give you the option to select different 'delimiters' such as Tab, Semicolon, Comma, Space or a character you have specified:
In our example, we will want to select 'space' as this is the character between our first and last names. Excel will then preview where the split will occur once the delimiter has been selected:
Fixed Width
Fixed-width text is where each column would be a set number of characters. You can draw a line down the column to separate the single column into multiple. If your text doesn’t look like it’s fixed width, try changing the font Courier. This method is a lot less practical than the 'delimited' option, so is very rare that you can make use of this feature. It is mainly used to break up uniform data that have been exported from another program and won't work with things like names as these have a different number of characters more often than not.