• 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.

Capturing the second impression:

Abhijeet R. Joshi

Abhijeet R. Joshi

Active Member
Hi all,

Is there a way to capture the second instance of a word/string in a huge string?

Example: Apple Brown Crawl Down Elegantly Apple Fall Below the Orange
In this I wish to capture the word "Brown" and "Fall" in cells B1 and C1 considering that the above big string is in Cell A1.

I wish to have a formula based solution.

Hope someone has an answer to this...
 
Try this..

=TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A1,"Apple ",REPT(" ",LEN($A1))),LEN($A1)*(COLUMNS($A:A))+1,LEN($A1)))," ",REPT(" ",100)),99))
 
I'm sure there's other ways to do this, but formula in B1:
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"Apple ",CHAR(160))," ",REPT(" ",999)),FIND(CHAR(160),SUBSTITUTE(SUBSTITUTE(A1,"Apple ",CHAR(160))," ",REPT(" ",999)))+1,999))

Formula in C1:
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"Apple ",CHAR(160),2)," ",REPT(" ",999)),FIND(CHAR(160),SUBSTITUTE(SUBSTITUTE(A1,"Apple ",CHAR(160),2)," ",REPT(" ",999)))+1,999))

The SUBSTITUTE function let you indicate which instance of the word/character you want to look for.
 
Abhijeet R. Joshi said:
So why have we used REPT(" ",999") in the above UDF?
Click to expand...

We're purposely adding some large spaces to help separate the words. Since we have such large gaps, we can then be less precise when we "cut" out a section that we know contains the portion of interest. The TRIM function then removes all the extra spaces.

For instance, let's take the 3-word phrase "Hi there Chandoo!"
If I replace all the spaces with REPT(" ",999"), then even though I don't know the exact amount, if I do:
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",999)),999,999))
I'll be starting at the 999 space (which I'll by 99.9% sure is a space, and then counting out another 999 characters (which should get me into the second large blank), thus extracting the text of interest ("there"). The TRIM like I said then removes all the extra spaces and makes it look all pretty.
 
Hi Luke,

Was just going thru my old threads and have a small query on the above mentioned by you...
Say we are not sure about the instances but want to capture the text after the last instance?

Example:
(Mike/Alpha/Charlie/Foxtrot) :here I want to capture only after the text after the last "/", but there might also be a scenario where there is a short string like (Mike/Foxtrot) so here basically the instances of "/" are not pre-defined and may vary....Can you please help me on this?
 
You must log in or register to reply here.
Back
Top