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:


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


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:


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 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.