• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

extract text between 2 identical characters in a cell

I

Injinia

Member
Hi

I would like to extract data between two identical words in an excel cell.

The length of the string varies in each cell. The occurrence of the word "or" happens 2/3 times in a cell in my data sample. example APPLE or ORANGE or PEAS

I used this formula to extract all words before the first occurrence of "or"

=LEFT($A6; SEARCH("or";$A6;1)) = APPLE.

How can I extract "ORANGES" and "PEAS" to their own cells?

Help much appreciated.
 
Since you did not provide file I assumed that there is always 1 space between each word including "or" also that word Orange is with capitalized Or so it wont match the same as "or". in this case for second word

=MID(A6,SEARCH("or",A6)+3,FIND("or",RIGHT(A6,LEN(A6)-SEARCH("or",A6)-2))-2)

third word

=RIGHT(A6,LEN(A6)-FIND("#",SUBSTITUTE(A6," ","#",LEN(A6)-LEN(SUBSTITUTE(A6," ","")))))

just replace commas with semicolons for your excel
 
tomas said:
Since you did not provide file I assumed that there is always 1 space between each word including "or" also that word Orange is with capitalized Or so it wont match the same as "or". in this case for second word

=MID(A6,SEARCH("or",A6)+3,FIND("or",RIGHT(A6,LEN(A6)-SEARCH("or",A6)-2))-2)

third word

=RIGHT(A6,LEN(A6)-FIND("#",SUBSTITUTE(A6," ","#",LEN(A6)-LEN(SUBSTITUTE(A6," ","")))))

just replace commas with semicolons for your excel
Click to expand...

Thanks alot!

This works brilliantly for the second word. I just noticed the "third word" might be a sentence. eg PEAS AT THE SHOP. in this case, it doesn't seem to work. How can I get it to work for the sentence scenario?
 
You must log in or register to reply here.
Back
Top