Convert google sheets formula to excel with an added functionality

[WaterAlex]

Hello all. thanks in advance for your help!

I have the following google sheets formula which I need make work for Excel:

=IFERROR(FILTER({TARGET!A:A, TARGET!C:D,PROPER(TARGET!E:E), SPLIT(PROPER(TARGET!G:G), ", ", FALSE), PROPER(TARGET!H:H), TARGET!I:I, TARGET!L:L, PROPER(TARGET!M:M), TARGET!Q:Q, TARGET!S:S, TARGET!U:U}, NOT(REGEXMATCH(TARGET!E:E, TEXTJOIN("|", TRUE, Blacklist!A:A)))))

What is does:

- Filters out rows that contain words in column E that are on the "Blacklist" tab

- Applying the PROPER function to columns E, G, H and M.

- Spliting the Column G using ", " as a delimiter. Most will have two commas, so this will create two new columns (three in total), pushing the adjacent columns to the right (not overwriting them). 

Regarding the SPLIT function, there are two more actions I'd like to integrate into the formula, which I still haven't been able:

- change the Headers for the three columns to: "City", "State", "Country"

- In the cases where the there aren't two delimiters (commas), but only one or none... the default for the data to stay in the original Column (furthest to the left).... Instead I need this data to populate towards the furthest to the right.

To better visualize this, here is a link to a sample google sheet. I've left the actual current result in the "RESULT" tab and the desired result in the "DESIRED RESULT" tab:

https://docs.google.com/spreadsheets/d/1n3Y51RipHTXmQ8I9PKANCFJLJUpaQavqHrEXJO9TqRk/edit?usp=sharing

[#2]

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