MS Excel: Nesting SUBSTITUTE & CONCATENATE Formula

I'm struggling with the final part of this formula to create a username for employees based off their name, country code where they reside, and employee ID.

I need to do this ONLY for users who aren't marked as speaking English in the spreadsheet as well. The cell may contain their language as Dutch or Swedish for example.

The data format needs to be as follows:

• FIRSTNAME initial in Uppercase
• LASTNAME in lowercase
• COUNTRY ABBREVATION
• Employee ID number

(Ex would be JdoeUS.720)

So far I am able to compile their first initial and last name, but I'm not sure how to add on the country abbreviation followed by their employee ID in here. I was trying to use a concatenate formula as well but with no luck. This is all I have:

=IF(COUNTIF(V5,"*English*"),"",PROPER(LEFT(D5)&TRIM(RIGHT(SUBSTITUTE(F5," ",REPT(" ",99)),99))))

This is my first time posting, I'm already overwhelmed and not sure I read the guidelines properly. I can add data from cells easily but that's about as far as my skill level goes.

With this formula in cell F2 and drag down, can do

=IF(E2<>"English",PROPER(LEFT(A2)&B2)&UPPER(C2)&"."&D2,"")

Not about the scope of your task, but using names or newer features like LET will make the formulas easy to understand and maintain.

Black cat's may be sufficient for your needs. But if you want to use LET etc. for this and future needs, please try entering this in the first cell where the results should appear (included a suggestion to handle what Chronocidal mentioned):

=LET(
    comment_1, "To automatically include future entries, specify start and ending rows",
    entry_start, 5,
    entry_end, 500,
    entries, LAMBDA(col,
        LET(
            start, INDEX(col, entry_start),
            end, INDEX(col, entry_end),
            start:INDEX(col, entry_start + COUNTA(start:end) - 1)
        )
    ),
    comment_2, "Remove spaces and accent chars",
    strip, LAMBDA(from_string,
        REDUCE(
            from_string,
            {" ", "'"},
            LAMBDA(stripped, remove_this, SUBSTITUTE(stripped, remove_this, ""))
        )
    ),
    firstname_initial_uppercased, UPPER(LEFT(entries(D:D))),
    last, entries(F:F),
    lastname_stripped_lowercased, LOWER(strip(TEXTAFTER(last, " ", , , , last))),
    languages, entries(V:V),
    speaks_english, MAP(languages, LAMBDA(lang, COUNTIF(lang, "*English*"))),
    country_abbr, entries(W:W),
    employee_id, entries(X:X),
    username_format, "JdoeUS.720",
    username, IF(
        speaks_english,
        "",
        firstname_initial_uppercased & lastname_stripped_lowercased & country_abbr & "." & employee_id
    ),
    username
)

These are helpful when editing long formulas:

Start a new line of text inside a cell in Excel - Microsoft Support

Excel Labs | Microsoft Garage