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.
