# TEXT Formulas: LEN(), RIGHT() & LEFT()

LEN()

The text in a cell characters (letters, numbers, spaces and symbols) are in your text. You enter this as =LEN(cell reference) e.g. =Cell(D4). On its own it has a limited use but in combination with RIGHT() or LEFT() it becomes far more useful.

RIGHT()

The RIGHT() formula will extract a number of characters from the rightmost part of the text in a cell. The formula is written =RIGHT(text, number of characters) In the example on the right our downloaded text has the three characters “ > “ in front of each name and we wish to use the name only in the rest of the workbook. The names are the rightmost part of the data but vary in length. LEN() will tell us the total length of the text including the name and the three additional characters at the start. Therefore, the length of the name must be three less than the total length and we can determine this with =Len(D5)-3. We can use this piece of information to extract the name using it to tell the RIGHT() formula how many characters to extract: =RIGHT(D5,LEN(D5)-3)

LEFT()

The Left() formula will extract a number of characters from the left most part of the text in a cell. The formula is written =LEFTT(text, number of characters).

In the example we have downloaded data in column C which has produced text containing both a number and a City Name. Nearly always system number identifiers (product code, employee number, place code etc ) are set to a standard length and in this case the code for each city is 6 numbers long. Therefore we can use the LEFT() formula to extract the city number code: = LEFT(c29,6).

Note: there is also an example of using RIGHT() combined with LEN() to extract the city name: =RIGHT(C29,LEN(C29)-6).