Text Functions
To see samples of the statistical functions, download functionsamples07.xls (2007) or functionsamples.xls (2003). They really need a decent table to be fully appreciated!
- FIND
- Syntax: FIND(find text ,within text, start with num)
Locate text within a text entry and returns the number of the starting position of the text within that entry.
- LEFT
- Syntax: LEFT(text, num chars)
Returns the number of leftmost characters specified.
- LEN
- Syntax: LEN(text)
Returns the number of characters in a text entry.
- MID
- Syntax: MID(text, start num, num chars)
Returns the specified number of characters from a text entry, beginning with the specified character number.
- RIGHT
- Syntax: RIGHT(text, num chars)
Returns the number of rightmost characters specified.
Use the text functions in combination to work with variable text lengths
in a column. This formula, for example, will always return the text after
the first comma in a text entry:
=RIGHT(A14,(LEN(A14)-FIND(",",A14)-1))
Using the first example above, it works by taking the length of the text (53) and subtracting the number of characters up to the comma (28) and an extra 1 for the space, and then returning that number (24) of characters from the right end of the text.
Download the sample workbook functionsamples.xls or for 2007 users functionsamples07.xls.
