How to determine the number of characters or length of cell in excel.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
Length of cell in excel
The LEN functions tells you the length of cell in excel or how many characters make up a cell. In this example we would like to extract the second last and third last characters in each of these cells. As you can see everything is different lengths. One of the ways to get around this is to use the LEN function to identify how many characters make up each cell. So I can for example go use the function wizard, find the LEN function. You wil see that all it asks for is point me to the cell you want to count, we will now click on it, and say OK. I am told there are 35 characters in this cell.
Using this information we can now extract these two characters by using the MID function which you can find out more about on the website. So we activate the function wizard, go to the mid function, click OK, for text we point here, the start number, we know the cell now has 35 characters, so this zero is character 35, so we are going to tell it to start at character 35 but go back 1, 2, so there is minus 2, and we want it to show us those 2 characters so its only 2 characters we need to put here and when we say OK you will see we have extracted these characters.
Now when we copy these down, what we should get in each case is, the second last and third last characters, even though these are all different sizes.