How do you counting characters in a string in Excel?

I found this to be a very useful little function. This is one of those formulas that is easy but most people may not know it so maybe you can impress your boss with it one day. The following formula will count the number of "B"s, both upper and lower case, in the string in B1.

=LEN(B1)-LEN(SUBSTITUTE(SUBSTITUTE(B1,"B",""),"b",""))

How do I use the LEFT, RIGHT, and LEN functions in Microsoft Excel spreadsheets?

The LEFT or RIGHT function returns the leftmost or rightmost characters from a text value. Example:

=LEFT(A2,3)

If value in cell A2 were 184770 the formula would display the number 4.

=Right(B3,2)

If value in cell B3 were 16579 the formula would display the number 7.

LEN (length) returns the number of characters in a text string. Example:

=LEN(A2)

If the value in cell A2 is 16498 then the formula will display 5.


Combine the two in a single formula example. Say you have a single digit which indicates length in .125 increments. Double digits indicates length in whole inches AND .125 increments.

=IF(LEN(D2)=2,LEFT(D2,1)+(RIGHT(D2,1)*0.125),RIGHT(D2,1)*0.125)

If the length of the value of cell D2 (the length number) equals 2 (which means it is double digit), then add the left digit plus the right digit multiplied by 0.125, otherwise take the single digit and multiply by 0.125.