# textjoin ignoring words containing "A"

#### Onione

##### New Member
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### jasonb75

##### Well-known Member
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

#### Onione

##### New Member
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 thank taking to you the check for time THANK 1 2 taking you thank for the time to check

#### jasonb75

##### Well-known Member
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

#### Onione

##### New Member

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
... 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

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
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
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:

Replies
0
Views
75
Replies
0
Views
60
Replies
16
Views
449
Replies
2
Views
100
Replies
18
Views
250