'TEXTJOIN cells unless a cell contain part of a string

I have several tables that represent rooms in our office, each table includes a column of few cells contain workers names and a device named like the following "Device A", "device B" etc.

Table 1:

HR G1:G6
George
Ema
Benjamin
Johanna
Device A

Table 2:

Dev H1:H6
Josh
Jenna
Tom
Jessica
Device B

I want to summarize all their names exclude the device to a cell on a new table.

Table 3:

Room NAME A1:B3
HR George, Ema, Benjamin, Johanna
Dev Josh, Jenna, Tom, Jessica

The issue is that I can change the devices order so the names on the table can change often.

I want to use TEXTJOIN (or other tool) to combine all cells unless the cell includes "Device" in it, without specify exactly which one.

I tried to use REGEXMATCH, VLOOKUP, SEARCH, COUNTIF but they all return a list, so its not working well inside TEXTJOIN.

I've tried to use FILTER too but wildcard doesn't work there so I must provide the full name of the device.

Please your advice, Thanks in advance!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source