Home » How To » Excel Functions » 7 Excel Functions You Must Know to Work Smarter

7 Excel Functions You Must Know to Work Smarter

Ever wondered how your colleague quickly generated the pivot tables, graphs, or used text/string related functions in excel?

It’s easy, it’s just a matter of knowing how it works.

I will walk you through extremely useful excel functions required quite frequently while working on excel data. And these are must-know functions for every engineer.

In this post, I will cover string or text related excel functions. I have grouped related functions and hence will be easier to understand.



Please note, all these functions are not case sensitive.
eg len(text) and Len(text) or even LEN(text) all will function same.
Let’s start with basic text functions now-

1. Trim:

Functionality: This function removes leading and trailing spaces from the text.
Syntax- trim(text)
How-to- If you want to get rid of the leading and trailing spaces in string ” example “, you can use trim function.
(Note spaces in the beginning and end of string).

Trim excel function

As shown above, trim function returns a string without the spaces at the beginning and end of the string.
Note: It will not remove the spaces within the words.
eg trim(“hello world”) will NOT return “helloworld”

2. Len:

Quite handy function! If you know this function, you can use it in conjunction with other functions as well, which I will explain later here.
Functionality – This function returns the length of the string ie number of characters in the string.
Syntax– len(String)
How-to-If you want to check the length of text “example” in cell A2 as shown in below, use the function Len.
Len excel function

Note: If your string has space in the beginning and/or at the end of the string, it will be counted in total length.




eg len(“ example ”) =10 because there is a space in beginning and end of the string.

How to get rid of this problem if you already have such spaces in string? Let’s use the trim function we learnt just now.
Len excel function

Awesome, it trimmed the string first and then calculated the length of the string.

Always remember, inner functions are executed first and then outer functions.

In this case, trim(A2) gets executed and then Len function gets executed.

3. Left, Right, Mid:

These functions are used if you want to extract certain number of characters from the text in excel cells.

Left:

Functionality: Left function returns mentioned number characters from left side of the text.

Syntax- left(text,num_chars)

How-to:
Left excel function

Right:

Functionality: Right function returns mentioned number of characters from right side of the string

Syntax- right (text,num_chars)

How-to:
Right excel function

Mid:

Functionality: mid function returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

Syntax: mid (text,start_num,num_chars)

How-to:
Mid excel function

4. Upper, Lower, Proper Case:

These functions change the case of the text.

Upper:

Functionality: Upper function converts the text in upper case

Syntax: upper(text)

How-to:
Upper excel function

Lower:

Functionality: Lower function converts the text in lower case

Syntax: lower(text)

How-to:
Lower excel function

Proper:

Functionality: Proper function converts first letter of each word in text in capital letter.

Syntax: proper (text)

How-to:
Proper excel function

5. Replace and Substitute:

These functions are used to change certain parts of the text.

Replace:

Functionality: Replace function replaces part of a text string based on the number of characters you specify, with a different text string.

Syntax: replace (old_text,start_num,num_chars,new_text)

How-to:

Replace excel function

To use this function, you need to precisely know starting number of characters to be replaced. But to replace the value in text without knowing the starting number, “substitute” function can be used.

Substitute:

Functionality: This function is used to substitutes part of the old text to new text mentioned.

Syntax: substitute (text,old_text,new_text,instance_num)

How-to:





Substitute excel function

Search, Find:

These are used to search position of substring in the text.

Search:

Functionality: Finds one text value within another (not case-sensitive)

 

Syntax: search (find_text, within_text, [start_num])

Start_num is optional.

How-to:
Search excel function

  • You can use the wildcard characters — the question mark (?) and asterisk (*) — in the find_text argument. A question mark matches any single character;
  • an asterisk matches any sequence of characters.
  • If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Search excel function

  • If the value of find_text is not found, the #VALUE! error value is returned.

Find:

Functionality: Finds one text value within another (case-sensitive)

Syntax: FIND(find_text,within_text,start_num)

How-to:
Find excel function

  • FIND is case sensitive and don’t allow wildcard characters. If you don’t want to do a case sensitive search or use wildcard characters, you can use SEARCH.
  • If find_text is “” (empty text), FIND matches the first character in the search string (that is, the character numbered start_num or 1).
  • Find_text cannot contain any wildcard characters.

7. Concatenate:

Functionality: Another simple text function to join several text items into one text item

Syntax: concatenate(text1, [text2], …)

How-to

Concatenate excel function

Small tricks save time and improve performance!

Enjoy excelling.

Stay tuned to learn the pivot tables, graphs in my following post.

If you are struggling with any of these functions, do send me an e-mail, I’ll get back to you and help to solve it.

6 comments

  1. 8. VLOOKUP:
    very usefull function

  2. Many thanks…very useful

  3. Exactly what im using for piping , very helpful!

    Thanks

  4. Very verrrrry useful tricks. It may lead me going far away to holiday until a new shortcut will find.
    I tend to use these functions in all of my brilliant life. It will work!

  5. Very useful tricks thanks a lot

Leave a Reply

Your email address will not be published. Required fields are marked *

*