Top 10 Text Functions in Excel

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:

  1. Proper,
  2. Lower,
  3. Upper
  4. Left
  5. Right
  6. Mid
  7. Find
  8. Len
  9. Concatenate
  10. Text

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.

CONCATENATE

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.

TEXT function

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.

Conclusion

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 keySkillset Game.

Share on facebook
Share on twitter
Share on linkedin
Logo keySkillset

Free educational games

10x faster with the programs you need!

Learn new tricks at up to 5x the rate!

Improve your Excel, PowerPoint, Python, Financial Modeling Skills.