Hi all,
Need help with the above concern. Here is an example data source:
<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
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