Advanced Spreadsheets – Text Functions
Use text functions: left, right, mid, trim, concatenate.
In the ‘Function Wizard’, under the ‘Text’ category, we have some functions which can be used to analyze text values.
One such function is ‘LEFT’. This function returns a specified number of first characters from any word. This function takes two arguments, ‘text’ and ‘number’. The ‘text’ is the text value on which the operation needs to be performed and ‘number’ is the number of characters which are to be returned, starting with the first character up to the character at this number.
Below is an example. Here, the text chosen is ‘Science’ and the number is 3. So the end result, as has been shown is ‘Sci’.
We also have a ‘RIGHT’ function which works in exactly the same way as ‘LEFT’, but for one difference. Here the characters returned are from the end instead of being from the start. Other than this, everything else is same.
Another text function is ‘MID’; this function returns a partial text string from a text value. This function takes three arguments, ‘text’, ‘start’, and ‘number’. The text value from which the partial text string is to be determined is given by ‘text’, ‘start’ gives the position in the text value from where the part word is to be determined and ‘number’ gives the number of characters in the part word, the number of characters to be returned starting with the position specified by ‘start’.
Here is an example of the usage of ‘MID’.
The text value selected in this example is ‘Female’. The ‘start’ is given as 3 and the ‘number’ is given as 2. So starting from third character which is ‘m’ up to 2 characters from this character, the end result is ‘ma’. This is how ‘MID’ function works.
‘TRIM’ function simply removes any extra spaces between words in a text field. It takes just one argument, the text field containing a number of words and returns the same set of words, but by removing any extra spaces between these words.
Another text function is ‘CONCATENATE’. This function adds the text values present in different cells and returns a single text value by adding all the different values together. Here a simple example of ‘CONCATENATE’ function has been shown. The words ‘Science’, ‘Humanity’ and ‘Arts’ have been added together and have been returned as a single word.