Monday, December 14, 2009

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.