Extract multiple words that contain certain characters

[XL User]

Hi all,

Need help with the above concern. Here is an example data source:

PS1001 TX1001 TX1001 PS1001 SECTION BE 4 12 S

PS1001 TX1001 TX1001 PS1001 SECTION BE 4 12 S

PS1001 TX1001 TX1001 BS1001 SECTION BE 4 12 S

PS1001 TX1001 TX1001 PS1001 SECTION BE 4 12 S

PS1001 TX1001 TX1001 PS1001 SECTION BE 4 12 S

PS1001 TX1001 TX1001 PS1001 SECTION BE 4 12 S

PS1001 TX1001 TX1001 PS1001 SECTION BE 4 12 S

<colgroup><col></colgroup><tbody>
</tbody>

I want to extract all words which contain PS, or TX into one separate cell. Below are the formulas I have already tried:

=TRIM(MID(SUBSTITUTE(B11," ",REPT(" ",99)),MAX(1,FIND("PS",SUBSTITUTE(B11," ",REPT(" ",99)))-50),99))

=CONCATENATE(IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B7,FIND("~~",SUBSTITUTE(B7,"PS","~~",1)),LEN(B7))," ",REPT(" ",100),1),100)),""),",",IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B7,FIND("~~",SUBSTITUTE(B7,"TX","~~",2)),LEN(B7))," ",REPT(" ",100),1),100)),""),",",IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B7,FIND("~~",SUBSTITUTE(B7,"BS","~~",3)),LEN(B7))," ",REPT(" ",100),1),100)),""))

None seems to be working properly... Please help

 

[yky]

Is the example your provided a representative data, meaning it contains all the possible variations in your data? If not, people might come up with a formula that works on this particular example but may not work on other examples that you did not post.

 

[jtakw]

Hi,

Great point by yky, please give some Real examples of your data, the ones you posted are identical, how would that be?

The example formulas you posted are not relevant to your data, especially the 2nd one.

 

[shaowu459]

[contain PS, or TX] do you mean begin with PS or TX?

 

[shaowu459]

B1=IFERROR(INDEX(TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",99)),ROW($1:93)*99-98,99)),SMALL(IF(MMULT(--ISNUMBER(FIND({"PS","TX"},TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",99)),ROW($1:93)*99-98,99)))),{1;1})>0,ROW($1:93)),COLUMN(A1))),"")

please use Ctrl+Shift +Enter to run the formula

Copy formula to column C, D......untile formula returns "".

 

[XL User]

Thanks guys

Yes begins with PS or TX.

Basically, I need to show all words from a cell into another single cell based on a list of specific prefixes:

PREFIX
PS	PX	PT
TS	TX	TT
HS	HX	HT
BS	BX	BT

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[shaowu459]

=IFERROR(INDEX(TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",99)),ROW($1:93)*99-98,99)),SMALL(IF(MMULT(--ISNUMBER(FIND({"PS","PX","PT","TS","TX","TT","HS","HX","HT","BS","BX","BT"},TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",99)),ROW($1:93)*99-98,99)))),ROW(1:12)^0)>0,ROW($1:93)),COLUMN(A1))),"")

row(1:12) means there are 12 prefixes, if you have more, please update 12 to other numbers.

 

[XL User]

Awesome! Thanks!

 

[shaowu459]

If all the words begin with PS or TX, there is a another formula will work:

=IFERROR(TRIM(LEFT(SUBSTITUTE(MID($A1,SMALL(IF(MID(" "&$A1,ROW($1:99),3)={" PS"," TX"},ROW($1:99)),COLUMN(A1)),99)," ",REPT(" ",99)),99)),"")