Hello and welcome to A1 Excel! If you've ever had a data list where you needed one portion of the cell rather than the whole detail then this post is for you. I'll be covering a couple different methods I've used that make cell splitting easy and efficient.
To start with, we can split cells using the following formulas:
LEFT: this formula splits off any characters from the left side of the cell
RIGHT: this formula splits off any characters from the right side of the cell
MID: this formula splits off any characters from the middle of the cell
The formulas above can be customized for any number of characters to separate out.
Please see below for a few examples of how to best use these formulas:
LEFT Formula:
This example shows how to pull airline codes from flight numbers. All that is needed is selecting the cell for separation and determining the number of characters to pull from the cell (in this case all we need is to pull two characters). For reference the formula is =LEFT(A2,2) in case you would like to copy/paste into your own Excel file for practice.
Formula:
Result:
RIGHT Formula:
This example shows how to pull a state abbreviation from a data list. All that is needed is selecting the cell for separation and determining the number of characters to pull from the cell (in this case all we need is to pull two characters). For reference the formula is =RIGHT(A2,2) if you would like to copy/paste and try it on your own.
Formula:
Result:
MID Formula:
This example shows how to pull a zip code from a list of location related data. All that is needed is to select the cell and pinpoint where the data pull needs to start and for how many characters are needed to pull out. For reference the formula is =MID(A2,4,5) if you would like to practice on your own.
Formula:
Result:
A method that does cell splitting without maintaining the original data is using the Text to Columns function located in the Data menu:
Let's use the data table from the MID formula example here. If we wanted to split out the data into separate columns all you need to do is select the data, select Text to Columns, separate by commas, and follow the prompts as seen below:
Result:
This method is ideal for splitting out data from locations that involve city, state, zipcode; names (first and last), and more.
Overall, using formulas or the text to column function provide efficient means of splitting out cell data. I hope you find this useful!
Comments