Check String Length in Excel

This article will show you how to count the number of characters in a text string using Excel.  There are times when you may need to check how long the string in a cell is. Let’s say you were checking the length of accounts to make sure they are all in accordance with expectation (all accounts may need to be 8 characters).  Or you may wish to know the length of text in a cell to perform some other type of lookup.

LEN Formula

In Excel the LEN formulas is the go to formulation for trapping the number of characters in a cell or the length of a text string. It can come in handy when data does not match, so you can check the length of the text string to identify if there is differences.

The simplistic version of the above LEN formula, looks at the cell and returns the length of the cells text.  If there are leading or trailing zeros, then the LEN formula will count these also. 

The LEN formula can be very useful extracting the second word in a string.  If the word Department preceded the specific department then extracting the second word might be our goal.  The following example shows how this might be done.

The following LEN formula could be used in conjunction with the MID and the FIND Formula .

=MID( B2, FIND(" ",B2,1)+1, LEN(B2) - FIND(" ",B2,1))

The formula works as follows.  The MID formula is able to trap text stating at a certain character within a string.  For example you may want all the characters after the first 3 characters in the string.  The second word in the above example is what we are looking for.  The  part we want to trap is the space.  To trap the Space between word 1 and word 2 we can use the Find Formula. 

FIND traps the number of a particular character in a string “ “ in the above example.  Plus 1 character (+1) will give us the starting letter for the second word.

By trapping the position of the space plus 1, this can be used in the MID formula to generate determine where to start the text extract.  From the start of the second word.

The final argument is the MID formula is how many characters to extract. This can be the Length of the string minus the characters before the FIND character, which of course was the space. So the combination of the MID, FIND and LEN formulas will return the second word in any string.

NOTE - In the above example Department is a consistent length however the formula will work with variable lengths.