If you ever need to split data in one column into two or more columns, you can use the LEFT, MID and RIGHT Text functions.
- The LEFT function returns the first character or characters in a text string, based on the number of characters you specify.
- The MID function returns a specific number of characters from a text string, starting at the position you specify.
- The RIGHT function returns the last character or characters in a text string, also based on the number of characters you specify.
The alternative is to use the Text to Columns option which has been covered in previous tips; however this option will not work if the source data has formulas like a VLOOKUP.
Example exercise – Splitting data from one column to two or more
In the example below we use the LEFT, MID and RIGHT functions to split the System Numbers into Main Account Numbers, Account Numbers and Sub Account Numbers.
Note: Download the sample workbook to practice this exercise.
Applies to: Microsoft® Excel® 2007, 2010 and 2013.
The screen shots below will be used to illustrate this exercise.
1. To extract the Main Account Number using the LEFT function
- Select cell B2
- Enter the formula as =LEFT(A2,4)
- Copy the formula down
2. To extract the Account Number using the MID function.
- Select cell C2
- Enter the formula as =MID(A2,5,3)
- Copy the formula down
3. To extract the Sub Account Number using the RIGHT function.
- Select cell D2
- Enter the formula as =RIGHT(A2,2)
- Copy the formula down
The outcome should be as per the screenshot below.
