Excel Formula To Find Word Ending With A Particular Character

In this post we will see how to create excel formula that only prints out the words ending with a particular character.

There are times when we have our text data in columns and we are concerned with extracting only the words that ends with a particular character, let’s assume that particular character is dot(.)

In the example below we will see how to create an excel formula that extracts the word that ends with “.”, you can use this to extract words before any character of your choice e.g. “,”,”a” etc.




Step 1

Below is the sample data in which you can see column A has the data from which we want to extract words that end with dot “.”.

Step 2

We will write an excel formula to extract words that end with “.”.

The below formula uses a combination of different text functions in excel like LEN,FIND and SUBSTITUTE to scan the source data and returns the output.

=IFERROR(MID(A2,FIND(CHAR(160),SUBSTITUTE(LEFT(A2,FIND(“.”,A2)),” “,CHAR(160),LEN(LEFT(A2,FIND(“.”,A2)))-LEN(SUBSTITUTE(LEFT(A2,FIND(“.”,A2)),” “,””))))+1,FIND(“.”,A2)-FIND(CHAR(160),SUBSTITUTE(LEFT(A2,FIND(“.”,A2)),” “,CHAR(160),LEN(LEFT(A2,FIND(“.”,A2)))-LEN(SUBSTITUTE(LEFT(A2,FIND(“.”,A2)),” “,””))))),””)

LEN-Determines the length of the argument.

SUBSTITUTE-Replaces a particular character with a new character.

FIND-Gives the position of a particular character in the source data.

Copy this formula and paste in the cell B2 to extract words that end with a particular character.

Step 3

You can see that formula gives the word “dot.”

If you don’t want that particular character (“.” In this example) to be included in the output, then use a SUBSTITUTE formula to replace that particular character (“.” In this example) with blank as shown in below formula

=IFERROR(SUBSTITUTE(MID(A2,FIND(CHAR(160),SUBSTITUTE(LEFT(A2,FIND(“.”,A2)),” “,CHAR(160),LEN(LEFT(A2,FIND(“.”,A2)))-LEN(SUBSTITUTE(LEFT(A2,FIND(“.”,A2)),” “,””))))+1,FIND(“.”,A2)-FIND(CHAR(160),SUBSTITUTE(LEFT(A2,FIND(“.”,A2)),” “,CHAR(160),LEN(LEFT(A2,FIND(“.”,A2)))-LEN(SUBSTITUTE(LEFT(A2,FIND(“.”,A2)),” “,””))))),”.”,””),””)

You can see now that “.” Has been removed.

If you have some different character other than dot then replace every dot from the formula with the character of your choice.

Hope this helps.

Share The Knowledge

Random Posts

  • Excel Formula To Sum Values Between Two Dates

    find sum of values between two dates in excel, excel formula to get the sum of a column between two dates, sum formula between two dates,sum of all the values between two dates in excel

    Share The Knowledge
  • Find Asterisk In Excel

    In this post we  will learn about how to find asterisk in excel.We may find some special character and wildcard […]

    Share The Knowledge
  • Average In Excel Including Blank Cells

    This post demonstrates how to calculate average of numbers in excel taking blank cells also into considerations. For example, if […]

    Share The Knowledge
  • Excel Deselect Cells/All

    This post demonstrates how to deselect all the cells without changing the active cell in excel The features is available […]

    Share The Knowledge

Leave a Reply