The Text To Columns functionality in Excel is useful if you want to split a cell by a character within a cell.
For example, in the data below we want to split the User ID into two (or more if required) parts at the point of the dash in the cell value.
We want to achieve the below – splitting the cell into two parts at the point of the dash ‘-‘.
To do this:
- Highlight column B and select ‘Text To Columns’ from the Data tab. This will open up a new box (see below) with a Wizard to help you through the steps.
- Select ‘Delimited’ then Next to take you to Step 2 of 3.
- We now need to select our Delimiter – this is the character we are going to use as the splitting point of the cell. We can split cells by Tab, Semicolon, Comma, Space and pretty much any other character we like. In this case we want to split the cell at the dash, so need to select Other, then enter a dash into the box to the right. By doing this we get a preview of our data using out current options – see below.
- Then select Next to go to step 3 of 3.
- We can select the data format of the new fields and the location of the new data but in most cases we will just select Finish – see below. By default the split out data will be created in the next column to the right. We can always make any changes to the data location and format afterwards if required.
- After clicking Finish we will have a new column (without a title) and the original cell will have been split into two with the dash removed completely, as per below.
