Excel Important Formulas


How to get the text from the left of any specific item?

In the below example put the formula "=LEFT(A2,(FIND(":",A2,1)-1))" in B2 cell and fill down


Description of the formula:

The formula =LEFT(A2,(FIND(":",A2,1)-1)) in cell B2 is extracting the text from cell A2 up to the first occurrence of the colon (":"). Here's a breakdown of the formula:

FIND(":",A2,1): This part finds the position of the colon (":") in the text in cell A2, starting the search from the 1st character. The result is the position of the colon.

(FIND(":",A2,1)-1): It subtracts 1 from the position of the colon, getting the number of characters before the colon.

LEFT(A2, (FIND(":",A2,1)-1)): This extracts the left portion of the text in cell A2 up to the calculated position (number of characters before the colon).

So, in simpler terms, it retrieves the text before the first colon in cell A2 and places it in cell B2.


How to get the text from the right of any specific item?

In the below example put the formula "=RIGHT(A2,LEN(A2)-FIND(":",A2))" in B2 cell and fill down


Description of the formula:

The formula =RIGHT(A2,LEN(A2)-FIND(":",A2)) in cell B2 is extracting the text from cell A2 starting from the character after the colon (":") until the end of the text. Here's a breakdown of the formula:

FIND(":", A2): This part finds the position of the colon (":") in the text in cell A2. The result is the position of the colon.

LEN(A2) - FIND(":", A2): It calculates the number of characters remaining in the text after the colon by subtracting the position of the colon from the total length of the text.

RIGHT(A2, LEN(A2) - FIND(":", A2)): This extracts the right portion of the text in cell A2 starting from the character after the colon and continuing until the end of the text.

So, in simpler terms, it retrieves the text after the first colon in cell A2 and places it in cell B2.


How to get the complete text excluding any number of letters from the right or left?

Exclude letters from the right:

In the below example put the formula "=LEFT(A2,LEN(A2)-3)" in B2 cell and fill down


Description of the formula:

The formula =LEFT(A2,LEN(A2)-3) in cell B2 is extracting the text from cell A2, excluding the last 3 characters. Here's a breakdown of the formula:

LEN(A2): This part calculates the length of the text in cell A2.

LEN(A2)-3: It subtracts 3 from the length, giving the number of characters excluding the last 3.

LEFT(A2, LEN(A2)-3): This extracts the left portion of the text in cell A2 up to the calculated position (number of characters excluding the last 3).

So, in simpler terms, it retrieves the text in cell A2 excluding the last 3 characters and places it in cell B2.


Exclude letters from the left:    

In the below example put the formula "=RIGHT(A2,LEN(A2)-3)" in B2 cell and fill down



Description of the formula:

The formula =RIGHT(A2,LEN(A2)-3) in cell B2 is extracting the text from cell A2, excluding the first 3 characters. Here's a breakdown of the formula:

LEN(A2): This part calculates the length of the text in cell A2.

LEN(A2)-3: It subtracts 3 from the length, giving the number of characters excluding the first 3.

RIGHT(A2, LEN(A2)-3): This extracts the right portion of the text in cell A2 starting from the calculated position (number of characters excluding the first 3).

So, in simpler terms, it retrieves the text in cell A2 excluding the first 3 characters and places it in cell B2.

Here are some situations where the above two formulas might be important:

Data Cleanup: In cases where you have a consistent pattern in your data and want to remove a fixed number of characters from the beginning of each cell, this formula can help. For example, if you have a set of data where the first few characters are not relevant, you can use this formula to clean up the data.

Text Extraction: If you have a dataset where important information is located at the end of each cell's content, you can use this formula to extract that information selectively.

Standardization: It can be used as part of a process to standardize data. For instance, if there's a common prefix in a set of cells, and you want to normalize the data by removing that prefix, this formula helps.

Dynamic Text Manipulation: The formula is dynamic; the number of characters to exclude is calculated based on the length of the text in each cell. This means it can be applied to different cells with varying lengths.

Automation in Data Processing: When working with large datasets, especially in the context of automating data processing using Excel functions or VBA, such formulas become crucial for handling and manipulating data efficiently.

Remember, the importance of this formula depends on the specific requirements of your data and the tasks you're performing in Excel.


Earn money by survey (Trusted and relay able site)


How to get the specific text from the entire text from a cell in Excel?

In the below example put the formula "=IF(ISNUMBER(SEARCH("Traffic",A2)),"Traffic","")" in B2 cell and fill down


Description of the formula:

The formula =IF(ISNUMBER(SEARCH("Traffic",A2)),"Traffic","") is designed to check if the text in cell A2 contains the word "Traffic." Here's a breakdown of how it works:

SEARCH("Traffic", A2): The SEARCH function looks for the text "Traffic" within the contents of cell A2. If "Traffic" is found, it returns the position of the first character of the first occurrence; otherwise, it returns an error.

ISNUMBER(...): The ISNUMBER function checks if the result of the SEARCH function is a number. If it is, it means that "Traffic" was found in the text.

IF(..., "Traffic", ""): The IF function is used to conditionally return values. If the result of ISNUMBER(SEARCH("Traffic",A2)) is TRUE (meaning "Traffic" is found), it returns "Traffic." If it's FALSE (meaning "Traffic" is not found), it returns an empty string ("").

In simpler terms, this formula checks if the word "Traffic" is present in cell A2. If it is, it returns "Traffic"; otherwise, it returns an empty string. This kind of formula is useful for categorizing or extracting information based on the presence or absence of specific keywords in the text.


While doing excel data analysis, it might require to (i). count the number of rows that having data inside, (ii) what is the first blank row index (iii) what is the last non-blank row index, in that case below formulas will give us the solutions.



How to:

1. Count Non-Empty cells,

=COUNTA(A:A)

2. Get the Row Number of the First Non-Zero Value,

=ROW(INDEX(A:A,MATCH(TRUE,INDEX((A:A<>0),0),0)))

3. Sum of Row Numbers for All Non-Empty Cells,

=SUMPRODUCT(MAX((A:A<>"")*ROW(A:A)))

4. Get the Row Number of the First Zero Value?

=ROW(INDEX(A:A,MATCH(TRUE,INDEX((A:A=0),0),0)))

Description of the formula:

Count Non-Empty Cells in Column A (B2):

Formula: =COUNTA(A:A)

  • Description: This formula counts the number of non-empty cells in Column A. The COUNTA function is used to tally cells that contain any type of data, including numbers, text, errors, and blank cells.

Get the Row Number of the First Non-Zero Value in Column A (B3):

  • Formula: =ROW(INDEX(A:A,MATCH(TRUE,INDEX((A:A<>0),0),0)))
  • Description: This formula retrieves the row number of the first non-zero value in Column A. The INDEX and MATCH functions are combined to find the position of the first non-zero cell, and ROW returns its row number.

Sum of Row Numbers for All Non-Empty Cells in Column A (B4):

  • Formula: =SUMPRODUCT(MAX((A:A<>"")*ROW(A:A)))
  • Description: This formula calculates the sum of the row numbers for all non-empty cells in Column A. The MAX and ROW functions are used in conjunction with SUMPRODUCT to achieve this.

Get the Row Number of the First Zero Value in Column A (B5):

  • Formula: =ROW(INDEX(A:A,MATCH(TRUE,INDEX((A:A=0),0),0)))
  • Description: Similar to the second formula, this one fetches the row number of the first cell containing zero in Column A. The INDEX, MATCH, and ROW functions work together for this purpose.

Earn money by survey (Trusted and relay able site)