1. Remove the spaces from a text except single spaces between words
Sometimes the data are cluttered, dirty, with spaces where you do not want to have, 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 examine 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.
Link: TRIM function.
2. Remove a specified number of characters from a text, either left, right or at any other point
There are many times that you need to extract a few characters of a text, for example if the text says only the full name and what we need is the surname. To achieve such extraction we have 3 elementary functions.
A. To extract characters from the left, we will use the LEFT function
=LEFT("The wizard of Excel", 6)= "The wi"
=LEFT("Rafael Nadal", 5)= "Rafae"
The number reflects the number of characters to extract.
Link: LEFT function
B. To extract characters from the right, we will use the RIGHT function
=RIGHT("The wizard of Excel", 6)= " Excel"
=RIGHT("Rafael Nadal", 5)= "Nadal"
As before the number reflects the number of characters to extract.
Link: RIGHT function
C. To extract characters from any other point, we will use the MID function
=MID("The wizard of Excel", 3,6)= "e wiza"
=MID("Rafael Nadal", 3,6)= "fael N"
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.
Link: MID function
3. Know what today's date 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.
Link: 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 find out the maximum value in a given list. Here's an example.
=MAX(2,5,7,8) = 8
Link: MAX function
The MIN function find out the minimum value in a given list. Here's an example.
=MIN (2,5,7,8) = 2
Link: MIN function
5. Find out what the second (or third or fourth ...) higher or lower value in a list
In section 4 we saw how to calculate the maximum and minimum value in a list, but what if we wanted to know the second lowest value, or the fourth highest. Excel has prepared two functions to help.
SMALL function and LARGE function.
The first of these will help us find the k-th smallest value in a data set. For example, the 20th smallest number.
Example:
=SMALL({1,2,5,3,6}, 4)= 5 indicates that the fourth smallest value in the list is five.
Link: SMALL function
The second function will help us find the k-th largest value in a set of data. For example, the tenth highest number.
Example:
=LARGE({1,2,5,3,6}, 4)= 2, indicating that the fourth largest value of that list is 2.
Link: LARGE function
6. Convert text to uppercase, lowercase or proper name
Excel has 3 useful useful functions to alter your strings, either to convert them to upper case, lower case or proper name (the first letter of each word in upper case text). Let's check them out.
A. To convert your text to upper case use the UPPER function:
=UPPER("The wizard of Excel") = "THE WIZARD OF EXCEL"
Link: UPPER function
B. To convert your text to lower case use the LOWER function:
=LOWER("The wizard of Excel") = "the wizard of excel"
Link: LOWER function
C. To convert your text to proper name use the PROPER function:
=PROPER("THE WIZARD OF EXCEL") = "The Wizard of Excel"
Link: PROPER function
7. Determine the week number in the year
As the year progresses we begin to lose what week number a given date is in the year. But Excel has a function to tell you exactly that.
Link: WEEKNUM function
With this function is easy to know which week of the year for example we are today, and that will depend on whether we want the week starts on Sunday or Monday.
=WEEKNUM(TODAY(), 1)
That was it. The next entry will be the 2nd part with more good Excel functions.
No comments:
Post a Comment