Short answer

Use REGEXMATCH(text, regular_expression) instead of = or EQ(value1,value2) to set the conditions for FILTER(range,condition1,[condition2, ...])

Demostration

Data source

+---+---------+---------+
|   |    A    |    B    |
+---+---------+---------+
| 1 | Field 1 | Field 2 |
| 2 | Yes     | A       |
| 3 | YES     | B       |
| 4 | no      | C       |
| 5 | No      | D       |
| 6 | NO      | E       |
| 7 | yes     | F       |
+---+---------+---------+

Formula in D1

=FILTER(A2:B7,REGEXMATCH(A2:A7,"^yes$"))

(The word is placed between ^ and $, so that the entire cell content is required to match it.)

Result

+---+-----+---+
|   |  D  | E |
+---+-----+---+
| 1 | yes | F |
+---+-----+---+

Short answer

• Use ARRAYFORMULA and the following to build a single formula solution:

  • Use REGEXEXTRACT and the array handling feature of Google Sheets to split column values

  • Do the required conversions

  • Use QUERY, TRANSPOSE & SUBSTITUTE to join two or several columns instead of CONCATENATE, JOIN & SPLIT.

Explanation

ARRAYFORMULA could work in a similar way than "for each item in array" loop, but not all Google Sheets functions are able to handle arrays as is the case of CONCATENATE, SPLIT and JOIN. Fortunately, REGEXTRACT could be used to split an indeterminate number of row values into columns and QUERY could be used to join two or several columns by using the headers argument to include all the rows as headers.

As QUERY will add a space character as separator of the joined headers, we will need to remove it.

1. Split the row values into columns by using REGEXTRACT and the array handling feature of Google Sheets.
2. Convert the values
3. TRANSPOSE the previous result
4. Join all the rows by using QUERY
5. Remove the space by using SUBSTITUTE
6. TRANSPOSE the previous result to get one column

Example

Assume that the column with hexadecimal Unicode code-points is column A and the values starts at A1 and ends at A7

=ARRAYFORMULA(TRANSPOSE(SUBSTITUTE(QUERY(TRANSPOSE(IFERROR(CHAR(HEX2DEC(REGEXEXTRACT(" "&A1:A7,REPT(" +[^ ]+",COLUMN(OFFSET(A1,,,1,6))-1)&" +([^ ]+)"))))),"SELECT *",COUNTA(A1:A7))," ","")))

Notes:

• The 6 in COLUMN(OFFSET(A1,,,1,6))-1 gives as result {0,1,2,3,4,5} because it's inside of ARRAYFORMULA. This determines the maximum number of elements by row in the split. Calculating this number inside the formula will make the spreadsheet slow to recalculate.

• Using open ended references (A:A) will work but also will make the spreadsheet slow to recalculate.

References

• Answer by AdamL to ARRAYFORMULA() does not work with SPLIT()
• Split a column of strings with a single formula returning an array