A formula is an equation designed by people in Excel.
A function is a predefined calculation in Excel.
FORMULA
Let's look at this data set
ADDITION
let's find the sum of sales from Table I
MULTIPLICATION
Let's multiply sales of Avalon Interactive and Avanquest.
DIVISION
Excel also follows BODMAS rules.
We can check the calculations from the formulas tab.
Then go to Evaluate Formula option.
FUNCTIONS
CLEAN FUNCTION
It removes all the non-printable text and returns the result.
You can see text like a question mark in a box that denotes non-printable text.
So by using this function, we can eliminate non-printable text.
This is how we solve this issue.
LEN FUNCTION
It returns the length of the string.
So it appears the clean function did not eliminate all special characters.
SUBSTITUTE FUNCTION
Now if we check the length it's 4.
REPLACE FUNCTION
It is used to mask data.
CONCATENATE FUNCTION
It is used to combine text or strings.
But if I copy this for all rows
Extra spaces are generated.
TRIM FUNCTION
This function removes all the extra spaces.
All extra spaces are trimmed.
UPPER, LOWER & PROPER FUNCTION
Convert a string to upper and lower case.
LEFT, MID & RIGHT FUNCTIONS
Used to fetch the left or right substring of a string.
VALUE FUNCTION
TEXT FUNCTION
Converts number to text.
SEARCH FUNCTION
Searches for a text within a cell.
TEXT JOIN FUNCTION
Used to join all rows into a single column.
TEXT-SPLIT FUNCTION
