Functions for the text values in Excel® 2010
When it comes to using formulas for numbers in Excel you have many options and are familiar with them. But what if you have text in the cells and you need to perform actions on those values? Hide your worries for now and let's see some of the formulas which can be used for text values in Excel.
Lower and Upper - When you have text jumbled in both kinds of letter cases, these functions can be used in converting the text into lower case or upper case respectively. The syntax is very simple to remember and use.
‘= LOWER(<cell reference>)’ and ‘= UPPER(<cell reference>)’
When the above functions are used, the text in the given cell is changed to the respective letter case.
Concatenate - Combining text from various cells becomes easy when you use the concatenate function. This function can be used on a text string, or on the single-cell references. Ampersand function also returns the same value or output as the concatenate function.
‘= CONCATENATE(text1,text2)’ and ‘= A1&B1’
The output for above two functions is the same. The text stings are attached and displayed.
Trim - Trim function removes the all the spaces in text strings, except for the single space between the words and displays them as output.
Example: ‘=TRIM(“Delegate your authority”)’ – Output would be ‘Delegate your authority’
Proper - This function capitalizes first letter in each text string and displays them as the output. This can be applied when you have names both, last and first names in a cell.
Example: ‘=PROPER(john smith)’ - the output would be ‘John Smith’
Clean - When you import data from any webpage, some non-printable characters are also imported. This function is used to clean up those non-printable characters in the cell.