Lesson#110: Separate characters from the text where any word appears

Here I will show how to separate characters from the text where any text appears.

Here in the above picture, I am having some text values which contain the word “made”. I will show you how to part out text value from where the word appears.

So I will find out the location of the word and separate the text from there. The formula will be like this

=MID(A1,FIND(“made”,A1),LEN(A1)-FIND(“made”,A1))

See how the formula is working. Now I will separate the part which comes before the word appears.

So the formula will be like

=TRIM(LEFT(A1,FIND(“made”,A1)-1))

This formula is separating the part of the text which comes before the word “made”. But these formulas only work where the word “made” comes only once or at the first occurrence of the word. If you need to separate text from the 2nd or 3rd or nth occurrence of a word then you need a different formula.

So I made formula with the SUBSTITUTE function which replace the 2nd occurrence of the word “made” with “*made” (Added an odd character to find out with the FIND function). This formula will be like

=MID(SUBSTITUTE(A1,”made”,”*made”,2),FIND(“*made”,SUBSTITUTE(A1,”made”,”*made”,2))+1,LEN(SUBSTITUTE(A1,”made”,”*made”,2))-FIND(“*made”,SUBSTITUTE(A1,”made”,”*made”,2))+1)

See this formula separate text from the second occurrence of the word where the word appears twice or more. Now how to separate before the 2nd occurrence of the word?

Use this formula there to separate before the 2nd occurrence of the word.

=TRIM(LEFT(SUBSTITUTE(A1,”made”,”*made”,2),FIND(“*made”,SUBSTITUTE(A1,”made”,”*made”,2))-1))

See the formula works well. I will show you a more useful formula in my next posts. Keep following this blog.