My students come to me with all sorts of questions. 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 and then do the same throughout the entire column which sometimes could reach thousands of rows. I cry when they explain how they currently do it; which 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. Then when they are finished and sure they have what they want they delete the original data in the previous column.
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.
How to use the Left function
Here is an example of the data as it is now.
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 that 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, there are always pitfalls to each way. Hope this tip has been useful to know about and spurs you on to do more.