Top 10 Text Functions in Excel

 In Uncategorized

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.

top text functions
top text functions
Use the LOWER Excel function to make all letters lower case, and UPPER function to make all letters uppercase.
top text functions

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).
top text functions
top text functions
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).
top text functions

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.
top text functions
As we can see, “k” is the 5th character in the whole text string “Hot KEY EXCELLENCE”.
top text functions
With LEN Excel function, you can find the length of the specified text string. For example, there are 18 characters in the cell A2.
top text functions

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.
top text functions
Notice, we need to add the space in between.
top text functions
You can do that by adding the space in parenthesis in between. Don’t forget to separate it with the commas on both sides.
top text functions
top text functions

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.
top text functions
top text functions

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 Hot Key Excellence Game.

Play Game Now

Recent Posts

Leave a Comment

How can we
help you?
Contact Us

Please, leave a message and we'll get back to you asap

0

Try Excel Game

for FREE