Extract multiple words that contain certain characters

XL User

New Member
Joined
Apr 11, 2018
Messages
8
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,878
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 26, 2018
Messages
570
Office Version
  1. 365
Platform
  1. Windows
[contain PS, or TX] do you mean begin with PS or TX?
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
570
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Apr 11, 2018
Messages
8
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
PSPXPT
TSTXTT
HSHXHT
BSBXBT

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

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
570
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Code:
=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.
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
570
Office Version
  1. 365
Platform
  1. Windows
If all the words begin with PS or TX, there is a another formula will work:
Code:
=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)),"")
 
Last edited:

Forum statistics

Threads
1,148,291
Messages
5,745,897
Members
423,983
Latest member
blackworx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top