Convert google sheets formula to excel with an added functionality

WaterAlex 

To convert the given Google Sheets formula to Excel and incorporate the additional functionalities, follow these steps:

Step 1: Open the Excel worksheet where you want to apply the formula.

Step 2: In the desired cell, enter the following formula:

=IFERROR(FILTER({TARGET!A:A, TARGET!C:D, PROPER(TARGET!E:E), IFERROR(IF(LEN(TARGET!G:G)>0, LEFT(TARGET!G:G, SEARCH(", ",TARGET!G:G)-1), TARGET!G:G)), IFERROR(IF(LEN(TARGET!G:G)>0, MID(TARGET!G:G, SEARCH(", ",TARGET!G:G)+2, SEARCH(", ",TARGET!G:G, SEARCH(", ",TARGET!G:G)+1)-SEARCH(", ",TARGET!G:G)-2), IF(LEN(TARGET!G:G)>0, MID(TARGET!G:G, SEARCH(", ",TARGET!G:G, SEARCH(", ",TARGET!G:G)+1)+2, SEARCH(", ",TARGET!G:G, SEARCH(", ",TARGET!G:G, SEARCH(", ",TARGET!G:G)+1)+1)-SEARCH(", ",TARGET!G:G, SEARCH(", ",TARGET!G:G)+1)-2), "")), "")), PROPER(TARGET!H:H), TARGET!I:I, TARGET!L:L, PROPER(TARGET!M:M), TARGET!Q:Q, TARGET!S:S, TARGET!U:U}, NOT(ISNUMBER(SEARCH(TRANSPOSE(Blacklist!A:A), TARGET!E:E)))))

Step 3: Press Enter to apply the formula.

Explanation:

The formula used in Excel is similar to the one used in Google Sheets, with some modifications to adapt to Excel syntax and achieve the desired functionalities.

The FILTER function remains the same, and the IFERROR function is used to handle any errors that may occur.

To split the text in column G using the delimiter ", ", the IF and SEARCH functions are used. If there are two delimiters, the text is split into three columns as desired. If there is only one or no delimiter, the original text remains in the leftmost column.

The PROPER function is applied to columns E, G, H, and M to capitalize the first letter of each word.

To filter out rows that contain words from the "Blacklist" tab, the ISNUMBER and SEARCH functions are used in combination with the TRANSPOSE function to search for each word in the entire column E. If a match is found, the row is excluded from the result.

Please note that the formula assumes that the "Blacklist" tab is located in the same workbook and the column references (e.g., TARGET!A:A) should be adjusted accordingly based on the actual sheet names and column ranges in your Excel workbook.

By following these steps, you should be able to replicate the functionality of the original Google Sheets formula in Excel with the added functionalities of splitting the text in column G and filtering rows based on the "Blacklist" tab. Text, steps and formula were created with the help of AI.

I don't know if that helps, I hope so, because it's better to give a non-precise or not-so-helpful answer than none at all. File is only downloaded and included in case someone wants to put it out there.