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,""))
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,003
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Onione

New Member
Joined
Apr 4, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
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
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,003
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Onione

New Member
Joined
Apr 4, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,886
Office Version
  1. 365
Platform
  1. Windows
... 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),"|","")
 

Onione

New Member
Joined
Apr 4, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,886
Office Version
  1. 365
Platform
  1. Windows
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),"|","")
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,003
Office Version
  1. 2019
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,114,117
Messages
5,546,038
Members
410,721
Latest member
adi772
Top