TextJoin + If Statement

[AECJohn]

Hi,

I am trying to work with the below formula:
=IF(TEXTJOIN("",1,AE3:AO3)>0,IF(ISNUMBER(SEARCH(CI1,A1)),TEXTJOIN("",1,AE3:AO3),"No"),"No Bank")
The range of AE3:AO3 should return the name of only one bank; this is a report that was exported to Excel from PDF and it did not put them all in the same column. If there is a bank in the range, it works, but the "No Bank" is where I am running in to problems.

I want to identify which cells do not have a bank name with the "No Bank". The "No" is a filler for rows that the formula does not apply to (there are six rows between each line where the formula applies).

I have tried <>0,=0,<0 and ISBLANK. None of them work.

Any insight would be appreciated!

 

[bobsan42]

Check what's in the cells - they are probably not blank. Do not compare with 0 ( a number) , but with a string, like "".
Try:
TRIM(TEXTJOIN("",1,AE3:AO3))<>""
Depending on what symbol you have in the seemingly blank cells, this may not work.
So examine these cells with LEN() and CODE() to establish what's inside.
It is also a good idea to examine the result from the TEXTJOIN() to come up with a working comparison.

 

[Fluff]

Can you post some sample data, so that we can see what you are dealing with.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.