Display the comment after finding the key word with a formula in an excel report

Most of the time I have a column with comments in my reports and it may happen that I need to know only some information after a specific word. In fact, I use this formula each time I need to display the most valuable data in order to discard the ones not important. It can be a comment, a number, a date, etc.

formula excel

 

When I use the formula ?

To display comments after finding some particular letters.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How is/are the formula(s) ?

=MID()

=FIND()

=LEN()

=INDEX()

=MATCH()

This formula shows a number of characters after finding the word:

=MID(A2,FIND("-",A2)+1,5)

Once this formula will find the score "-", it will display the 5 characters after it.

Explanation:

  • A2 is the cell where I ask it to search for the score
  • +1 is to tell from where to begin to display once it finds the key character, so if you want to include the score, remove it.
  • 5 is the number of letters you want to display, take note that a blank space is considering as 1 character.

This formula shows all information after finding the word:

=MID(A2,FIND("something",A2)+10,LEN(A2))

formula excel

Explanation:

  • A2 is the cell where I ask it to search for the word “something”
  • +10 is to display all comments after this word, the number 10 is corresponding of the total number of characters (“something ” is 10 including space but without space, “something” is 9)

This formula does the same thing as above, the only difference is that it is asking only to display comments for “chocolate”.

=MID(INDEX(A1:A10,(MATCH("chocolate",B1:B10,0))),FIND("-",INDEX(A1:A10,(MATCH("chocolate",B1:B10,0))))+1,5)

formula excel

Interesting Topics