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).

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.

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.

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:

Right:

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

Syntax- right (text,num_chars)

How-to:

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:

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:

Lower:

Functionality: Lower function converts the text in lower case

Syntax: lower(text)

How-to:

Proper:

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

Syntax: proper (text)

How-to:

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:

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:

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:

• 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.

• 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 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

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.