textjoin ignoring words containing "A"

Onione

New Member
Joined
Apr 4, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
I cant remember where I found this useful formula but it will take a range and join it with any words containing A in a column after fill down. I have been failing to change the code to work identically except instead of all words with A I would like to add all words that do not contain A. Can anyone help me out?

=TEXTJOIN("",TRUE,IF(MIN(IF((COUNTIF(B9:$B$9,$B$2:$D$4)=0)*ISNUMBER(SEARCH("A",$B$2:$D$4)),(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1,""))=(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1,$B$2:$D$4,""))
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Without a data sample (before and after) I wouldn't even attempt something like this, at a glance I'm sure that the formula in your post is far more complicated than it needs to be but without seeing the end result that is only a guess.
 
Upvote 0
Without a data sample (before and after) I wouldn't even attempt something like this, at a glance I'm sure that the formula in your post is far more complicated than it needs to be but without seeing the end result that is only a guess.

I am not great at formulas but I don't see how it could be less complicated. I have a feeling one of the logic tests need to change but I just cant figure it out. It works by fill down, 1 is filtering "a" 2 is how I would like it to work If you know a better way I would love to see it! I thought this formula was pretty neat. See below:




Range to filter
thanktakingto
youthecheck
fortimeTHANK
12
takingyou
thankfor
the
time
to
check
 
Upvote 0
Having seen what it is meant to be doing, I agree that it might not be possible to make it less complicated (with your version of excel), but from just a formula and a description it was not clear exactly what it was doing.

For now, changing ISNUMBER to ISERR will do what you need.

With office 365 (or possibly the next release of excel when it becomes available) a much shorter formula using FILTER and UNIQUE should work.
 
Upvote 0
Having seen what it is meant to be doing, I agree that it might not be possible to make it less complicated (with your version of excel), but from just a formula and a description it was not clear exactly what it was doing.

For now, changing ISNUMBER to ISERR will do what you need.

With office 365 (or possibly the next release of excel when it becomes available) a much shorter formula using FILTER and UNIQUE should work.

AWESOME! You nailed it on the head, works perfect and was a much simpler solution than I was expecting.
 
Upvote 0
... works perfect and was a much simpler solution than I was expecting.
I assume you mean the change to ISERR as shown in column B below. In column C I have suggested a considerably less complicated formula that I think should also do the job for you - unless you are particularly concerned about what order the results come in.

20 10 05.xlsm
ABCD
1
2thanktakingto
3youthecheck
4fortimeTHANK
5
6
7
8
9
10toto
11checkyou
12thethe
13youcheck
14timefor
15fortime
16  
Exclude A
Cell Formulas
RangeFormula
B10:B16B10=TEXTJOIN("",TRUE,IF(MIN(IF((COUNTIF($B$9:$B9,$B$2:$D$4)=0)*ISERR(SEARCH("A",$B$2:$D$4)),(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1,""))=(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1,$B$2:$D$4,""))
C10:C16C10=SUBSTITUTE(LEFT(TEXTJOIN(REPT("|",99),1,IF((COUNTIF(C$9:C9,B$2:D$4)=0)*(ISERR(SEARCH("A",B$2:D$4))),B$2:D$4,"")),99),"|","")
 
Upvote 0
I assume you mean the change to ISERR as shown in column B below. In column C I have suggested a considerably less complicated formula that I think should also do the job for you - unless you are particularly concerned about what order the results come in.

20 10 05.xlsm
ABCD
1
2thanktakingto
3youthecheck
4fortimeTHANK
5
6
7
8
9
10toto
11checkyou
12thethe
13youcheck
14timefor
15fortime
16  
Exclude A
Cell Formulas
RangeFormula
B10:B16B10=TEXTJOIN("",TRUE,IF(MIN(IF((COUNTIF($B$9:$B9,$B$2:$D$4)=0)*ISERR(SEARCH("A",$B$2:$D$4)),(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1,""))=(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1,$B$2:$D$4,""))
C10:C16C10=SUBSTITUTE(LEFT(TEXTJOIN(REPT("|",99),1,IF((COUNTIF(C$9:C9,B$2:D$4)=0)*(ISERR(SEARCH("A",B$2:D$4))),B$2:D$4,"")),99),"|","")

I like it thankyou. I'm now struggling trying to add multiple letters to ignore. is that possible? I have tried," ", &" "& and a bunch of other ways.
 
Upvote 0
If you were wanting to exclude "a" & "e"

20 10 05.xlsm
ABCD
1
2thanktakingto
3youthecheck
4fortimeTHANK
5
6
7
8
9
10to
11you
12for
13 
14 
Exclude A
Cell Formulas
RangeFormula
B10:B14B10=SUBSTITUTE(LEFT(TEXTJOIN(REPT("|",99),1,IF((COUNTIF(B$9:B9,B$2:D$4)=0)*(ISERR(SEARCH("A",B$2:D$4)))*(ISERR(SEARCH("E",B$2:D$4))),B$2:D$4,"")),99),"|","")
 
Upvote 0
If you wanted more than 2 exclusion strings then it might be a little easier to manage by using an array for the criteria instead of individual searches.
Excel Formula:
=SUBSTITUTE(LEFT(TEXTJOIN(REPT("|",99),1,IF((COUNTIF(B$9:B9,B$2:D$4)=0)*AND(ISERR(SEARCH([B]{"A","E"}[/B],B$2:D$4))),B$2:D$4,"")),99),"|","")
Also, you might need to change AND to OR depending if the requirement is words that contain any of the letters in the list or words that contain all of them.

Another observation, there has been no mention of array confirming the formulas. If you're not doing that then it means that you must be using office 365 with dynamic arrays. @Peter_SSs if that is the case, would a simpler formula be possible with FILTER and UNIQUE?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

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
Back
Top