Excel splitting cells
A common question is: “How to split Excel cells?” or in Dutch, “Hoe Excel cellen te splitsen?” ‘. We’re glad to address this query in this article. Splitting Excel cells can be particularly useful when, for example, you have a long list of names with both first and last names in a single column. Splitting cells in Excel can save you hours of work, especially when dealing with extensive lists, such as a column containing both first and last names. While you might think of dividing a single cell into two smaller cells within the same column, unfortunately, Excel doesn’t support this functionality. Instead, create a new column next to the one with the cell you wish to split, and then proceed to split the data across columns. You can also split the contents of a cell into several adjacent cells. Would you like to know the step-by-step process? Keep reading.
How to split Excel cells?
Depending on the content, a cell can be split into two or more cells. To illustrate, consider the following dataset:
- Select the cells you wish to split; in this example, cells A1:A5.
- Then go to ‘Data’-> ‘Text to columns’. The ‘Text to Columns Wizard’ will appear.
- Choose the ‘Delimited’ option, if it’s not already selected, and click ‘Next’.
- Choose the appropriate delimiter; in our example, it would be a semicolon. Then click next again.
- Set the datatype for each column. Typically, leaving it as ‘General’ will suffice. Choose the destination for the split cells. Be cautious, as existing data in the selected range will be overwritten. Finally, click ‘Finish’.
Splitting Cells Using Formulas
The ‘Text to Columns’ tool is helpful but may not always be suitable. At times, formulas may be more practical for text manipulation. Some useful formulas for text manipulation include:
- Extracting a certain number of characters from the beginning of a text.
- Extracting characters from the end of a text.
- Extracting characters from the middle of a text.
- Determining the length of text in a cell.
- CONCAT. Concatenating texts into a sentence.
- Converting numerical values to text.
- ALL Searches for a text or character in another text.
- Repeats a character a number of times.
Only finding the club in the previous example could be done in the following way:
Even for an intermediate or advanced Excel user, using formulas to split Excel cells can be challenging. However, practice makes perfect.
Looking for an Excel Expert?
As noted, splitting cells in Excel can be a complex task for the average user. So in this area, an Excel Expert will be happy to help you. Not getting anywhere and could use some extra help with your Microsoft Excel work? If so, please contact us with no obligation and who knows, we may soon start working with you.
