Today we are covering 7 very useful Excel functions not commonly used. Here it goes.
1. Remove all spaces from a text except single spaces between words
Sometimes the data is cluttered, dirty, with spaces where there shouldn’t be, right? As one of the most useful ways to help you clean your data is the TRIM function.
This function removes all spaces from a string except for single spaces between words. Let us see with an example.
=TRIM(“The wizard of Excel ”) = “The wizard of Excel”
The function has removed those spaces before, after and in between to leave the text clean and ready for analysis.
2. Remove a specified number of characters from a text, either from the left, right or at any other point
There are many times that you need to extract a few characters from a text, for example if the text says only the full name and what you need is just the surname. To achieve such extraction we have 3 basic functions.
2A. To extract characters from the left of a text, we use the LEFT function
=LEFT(“The wizard of Excel”, 6)= “The wi”
=LEFT(“I love Malta”, 5)= “I lov”
The number reflects the number of characters to extract.
2B. To extract characters from the right, we use the RIGHT function
=RIGHT(“The wizard of Excel”, 6)= “ Excel”
=RIGHT(“I love Malta”, 5)= “Malta”
As before the number reflects the number of characters to extract.
2C. To extract characters from any other point, we use the MID function
=MID(“The wizard of Excel”, 3,6)= “e wiza”
=MID(“I love Malta”,3,6)= “love M”
Now the numbers reflect different things. The first number reflects the position of the first character to be extracted, and the second number reflects the number of characters to extract.
3. Know what date is today rapidly
Imagine we have a list of orders and want to see what should be distributed today. For something like that we can simply use the TODAY function.
4. Find out the maximum or minimum value in a list
Excel has two functions for this: the MAX function and the MIN function.
The MAX function finds out the maximum value in a given list, whereas the MIN function finds out the minimum value. Here’s an example.
=MAX(2,5,7,8) = 8
=MIN (2,5,7,8) = 2
5. Find out the second (or third or fourth …) higher or lower value in a list
We have just see how to calculate the maximum / minimum value in a list, but what if we needed to know the second lowest value, or the fourth highest. Excel has prepared two functions to help.
SMALL function and LARGE function.
The SMALL function finds the k-th smallest value in a data set. For example, the 20th smallest number.
The LARGE function finds the k-th largest value in a set of data. For example, the tenth highest number.
Examples:
=SMALL({1,2,5,3,6}, 4)= 5 indicates that the fourth smallest value in the list is 5
=LARGE({1,2,5,3,6}, 4)= 2 indicates that the fourth largest value of that list is 2
6. Convert text to uppercase, lowercase or proper name
Excel has 3 very useful functions to alter your strings, either to convert them to uppercase, lowercase or proper name (the first letter of each word in upper case text). Let’s see them more in detail.
6A. To convert your text to upper case use the UPPER function
=UPPER(“The wizard of Excel”) = “THE WIZARD OF EXCEL”
6B. To convert your text to lower case use the LOWER function
=LOWER(“The wizard of Excel”) = “the wizard of excel”
6C. To convert your text to proper name use the PROPER function
=PROPER(“THE WIZARD OF EXCEL”) = ”The Wizard of Excel”
7. Determine the week number in the year
To find out the week number of a given date Excel has a function to tell you exactly that: WEEKNUM function
There are 2 systems to understand the week number:
System 1: the first week of the year (week 1) is the week containing January 1st.
System 2: the first week of the year (week 1) is the week containing the first Thursday of the year. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system.
Excel accommodates for both systems in the WEEKNUM function. To do so, the second part of the function indicates on which day the week begins.
| Return_type | Week begins on | System |
| 1 or omitted | Sunday | 1 |
| 2 | Monday | 1 |
| 11 | Monday | 1 |
| 12 | Tuesday | 1 |
| 13 | Wednesday | 1 |
| 14 | Thursday | 1 |
| 15 | Friday | 1 |
| 16 | Saturday | 1 |
| 17 | Sunday | 1 |
| 21 | Monday | 2 |
Example:
=WEEKNUM(“01/01/2012”, 1)=1
=WEEKNUM(“01/01/2012”, 2)=1
….
=WEEKNUM(“01/01/2012”, 21)=52
**************
Written by Oscar Toledano – I am passionate about Excel ever since I first used it. With over 15 years’ experience as a heavy user, I decided to set up The Wizard of Excel for users to have more information, techniques, tips and more about this incredible tool. Facebook, Twitter, and Google+.
Add CEOWORLD magazine to your Google News feed.
Follow CEOWORLD magazine headlines on: Google News, LinkedIn, Twitter, and Facebook.
Copyright 2024 The CEOWORLD magazine. All rights reserved. This material (and any extract from it) must not be copied, redistributed or placed on any website, without CEOWORLD magazine' prior written consent. For media queries, please contact: info@ceoworld.biz
