My students come to me with all sorts of questions. The one that seems to come up every so often is how to easily change the length of a piece of data in a cell to just a few characters. Then do the same throughout the entire column, which sometimes could reach thousands of rows. I cry when they explain that what they do at present is copy the information into the adjacent column and then edit each and every cell down the column one by one deleting the last few characters or the first whichever is not needed.
Well there is a lovely way to overcome this problem. Using the function “Left” to achieve the result of keeping the first characters in the string. Or using the function “Right” to keep the last few characters. Here is an example of the data as it is now. the data is in column D.
In the column displaying the Employee number what you actually require is just the first part not the long number of the second part.
- Insert a new column to the right of the one with the data we are going to extract from.
- Select the Insert Function tool.
This dialog box appears.
- Locate and open the LEFT function and this dialog box appears.
- Fill in the two rows like this.
D2 is the cell that holds the original data.
5 is the number of characters you require.
Note: Some of the data is shorter than 5 in my example.
The result is this.
- Now autofill down the column to get all the results.
Note: that the results are 5 characters where there is 5 in the data and 4 where there is only 4.
You can extract the numbers to the right of this entry in the cell D2 by using the RIGHT function in the same way.
This would be the result. Of course, there is always Flash Fill these days too. I have placed this at the end of the columns of the original data. You need to use what works for you. Investigate the “Mid function” which takes just the middle section of characters that you specify.