split and place specific word only

[kayza]

I have a sentence in cell A1 that contains a few words, but I just want to take a specific word and put it in another cell, for example B1 and so on


the sentence in cell A1 is as follows:
Name: one; Class: A; Add: yes; Name: two; Class: B; Add: no; Name: three; Class: A; Add: no


the results I want to display in cells B1 to B3 are as follows
Add: yes
Add: no
Add: no


What is the right formula to solve this problem


Thank you in advance

 

[admiral100]

Hi,

Try this and drag down :

B1=MID($A$1,FIND("*",SUBSTITUTE($A$1,"Add:","*",ROW(A1))),8)

 

[jtakw]

Hi,

If what you want extracted is Actually either "Add: yes" or "Add: no" without quotes, use this,
if the text string can be variable after "Add:" (i.e. Add: undetermined, Add: undecided, Add: unknown, etc.), let me know, I'll modify formula to suit:

Book1
	A	B
1	Name: one; Class: A; Add: yes; Name: two; Class: B; Add: no; Name: three; Class: A; Add: no	Add: yes
2		Add: no
3		Add: no
Sheet360

Cell Formulas
Range	Formula
B1	=IFERROR(TRIM(MID(SUBSTITUTE(A$1,";"," "),FIND("|",SUBSTITUTE(A$1,"Add:","|",ROW(B1))),8)),"")

Formula copied down.