Top 10 Text Functions in Excel
What Text Functions Can Excel Offer?
Excel has many functions to offer when it comes to manipulating text strings. So, let’s take a look at 10 most frequently used Text functions:
PROPER, LOWER and UPPER Excel Text Functions
Let’s say in the example below, you’d like to change the text in the cell A2 in a proper way, meaning that the first letters of each word would be upper case, and the rest letters would be lower case. For that purpose, we’d need to use the PROPER function in Excel.
Use the LOWER Excel function to make all letters lower case, and UPPER function to make all letters uppercase.
LEFT, RIGHT, and MID Excel Text Functions
Let’s say you need just part of the next in the cell A2, such as “Hot KEY” instead of “Hot KEY EXCELLENCE”. The best way here to use LEFT Excel function, as “Hot KEY” are the first letters to the left out of the whole text in A2. There are 7 characters in “Hot KEY” as we need to count the space as well. Therefore, when using the LEFT function and after referencing A2, we need to put 7 after a comma in our example (see picture below).
Same logic you can use for the RIGHT function, which would take the characters from the text from the right. Similar logic would apply for MID function, which will take the text from the middle: =MID(text, start_num, Num_char). You just need to show from which character you want to start and for how many characters. In our example, it would be =MID(4,3).
FIND and LEN Excel Text Functions
Use FIND function to return the position of a specific character within a text string. In our example, we want to find the position of “K” in the text string in the cell A2.
As we can see, “k” is the 5th character in the whole text string “Hot KEY EXCELLENCE”.
With LEN Excel function, you can find the length of the specified text string. For example, there are 18 characters in the cell A2.
Use CONCATENATE to join two or more text strings into one text string. In our example, we want to combine the text in cell A2 “Hot KEY” and in the cell A3 “EXCELLENCE” into one string in the cell B2.
Notice, we need to add the space in between.
You can do that by adding the space in parenthesis in between. Don’t forget to separate it with the commas on both sides.
The Excel TEXT function returns a number in a specified number format, as text. You can use the TEXT function to embed formatted numbers inside the text. For example, there is a TEXT function built in the cell C4, it is referencing cell C2. If we change the number in the cell C2 from $100 to $500, it will be automatically changed in the text of C4 as well.
Excel has many functions to offer when it comes to manipulating text strings. It’s very useful to know and use them to save your precious time.
Learn more Excel functions and tricks in the Hot Key Excellence Game.