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