5 text-based functions in Microsoft Excel 2010
On some occasions you would need to work with Text in Microsoft Excel 2010. Do you know a few important functions that you can use on text in Microsoft Excel 2010? Text functions are as important as the other functions that you use on Microsoft Excel 2010. So, here are 5 text-based functions in Microsoft Excel 2010 that we think you should know about.
This function is used when you have to join two or more strings together in one cell. This function can join up to thirty stings together once. The syntax for this function is ‘= concatenate (text1, text2, text3…)’. An alternative for this function is when you use ‘ampersand’ to join the values of two or more cells. And the syntax to use the ampersand is simple ‘= A1&B2’. In this example, the ampersand function will just combine the values of the cell A1 ad B2 and display it on the cell where the function is used.
This function allows you to replace the text in a string and returns / gives you a string with the replaced values. The syntax for this function is ‘= replace (old_text, start_num, num_chars, new_text). Let’s understand the syntax to see how the function works. Old_text is the text in which you need to replace the characters. Start_num is where the character in old_text that is to be replaced is present. Num_chars is the number of characters in old_text that you need to replace. And new_text is the new text that will replace the characters in old_text.
This function converts the number to text format and also applies the dollar currency symbol. This function can also be used to round off the numbers if you have a mixed bag of numbers. The syntax of this function is ‘=dollar (number, decimals)’. Here in this function, ‘number’ can be the reference to the cell where the number that’s to be converted to text is present. The ‘decimals’ refer to the number of digits to the right of decimal point. To round off the number, the value of ‘decimals’ should be negative.
This is one of the simple text functions in Microsoft Excel 2010. Lower is a function that converts a text string into lowercase. That’s all. It’s simple, right? The syntax for this function is ‘= lower (text)’. Here, text would be the reference of the text that’s present in the cell. So a cell number should be given there in the formula to convert the content of the cell into lowercase.
Trim function replaces the spaces from text. It doesn’t remove single spaces between the words but removed the extra spaces that might be present in the text in a cell. So this function comes handy whenever there’s irregular spacing in your data. The syntax for this function is ‘= trim (text)’. The text in this formula refers the complete text from which you need the spaces to be removed.