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

Copy/extract text from a cell based on defined keywords

T

tango

Member
I am in need of formula that would copy/extract from one column (A2-A4) and show the result in different cell/column (B2-B4) based on the defined keywords (H1).


SAMPLE DATA:

A2: The boy and girl is walking.

A3: the girl is good. the boy is good and tall. the mother is sleeping.

A4: Leo, Lea and the mother lives at the 27 street.


RESULT (somehow):

B2: boy, walking

B3: good, boy, mother

B4: leo, mother, 27


KEYWORDS (in one cell or can be list by column, whichever is easy to formulate.)

H1: boy, walking, good, leo, mother, 27


I found below related solutions but not able to put it together as intended above.

http://chandoo.org/forums/topic/copying-specific-text-from-one-cell-and-pasting-in-the-next-column-same-row

http://chandoo.org/forums/topic/need-help-creating-formula-array-to-look-for-text-in-cells

http://chandoo.org/forums/topic/taking-text-out-of-a-cell


Please help and thanks.
 
Hi Tango


From your post, it is not clear what you want to achieve. Can you please tell on what basis you are extracting the words from strings.


Amritansh
 
Thanks amrit1186...


1) "A1" contains full journal references.

2) "H1" or "H1:H20" contains few selected keywords.

3) "B1" needs to display the texts/words that appear in both "A1" and "H1"or"H1:H20".


"B1" should display the text appears on "H1"or"H1:H20" which is similar or found in "A1". I need formula to achieve the task required for "B1" please. Thanks.
 
Hi ,


There is no way that the list of concatenated words can appear in one cell , since the CONCATENATE function does not work with arrays.


What can be done is for the words to appear in several columns.


Assuming that your keywords are in a named range called Keywords_Range , you can try the following formula , entered as an array formula , using CTRL SHIFT ENTER :


=IFERROR(MID($A1,SMALL(IFERROR(SEARCH(Keywords_Range,$A1),999),COLUMN(A1)),FIND(" ",$A1&" ",SMALL(IFERROR(SEARCH(Keywords_Range,$A1),999),COLUMN(A1)))-SMALL(IFERROR(SEARCH(Keywords_Range,$A1),999),COLUMN(A1))),"")


Enter this in B1 , and copy it across , in C1 , D1 , E1 ,...


Copy it down , to B2 , C2 , D2 , E2 ,.... and beyond as far as your data is present.


This is not a fool-proof formula , since if the keyword is together with a period "." or a comma "," or any other punctuation mark , any character other than a space , that character will also appear in the output cell.


Narayan
 
You must log in or register to reply here.
Back
Top