SOLVED

# Help combining multiple formulas

Hi there,

If someone could tell me how to combine all these formulas I would REALLY appreciate it.

=CONCATENATE(LEFT(A2,1),B2)
=LOWER(E2)
=SUBSTITUTE(H2," ","")
=SUBSTITUTE(H2,"-","")
=SUBSTITUTE(H2,".","")
=LEFT(J2,8)

Column A is Firstname. Column B is Lastname. Ultimately, I trying to create usernames (first initial, last name, lower case, no spaces, no dashes, no periods, trim to first 8 characters).

I have been doing it one formula at a time, pasting values into the next column over, and then running the next formula. Etc., etc., etc. It is taking forever!

5 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Help combining multiple formulas

It takes less than a second.

``=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(LEFT(A1)&B1),"-","")," ",""),".",""),8)``

# Re: Help combining multiple formulas

@Detlef Lewin Thank you so much!!!! I'm sure you're not surprised that it works perfectly. And now that i've seen it done, it makes a lot more sense to me.

# Re: Help combining multiple formulas

I'd change the order a bit - first to clean, or at least trim, A1 and B1 separately and finally combine first character from former A1 and entire B1.

# Re: Help combining multiple formulas

Otherwise it could be # Re: Help combining multiple formulas

Another way of doing this without helper columns is to use the built-in function LET, which is available in versions of Excel since 2021.

You can define and calculate values for names (a.k.a. variables) within the function; I created three in the example below.  (Unlike named ranges and named formulas, these names only apply within the particular LET function within one formula.)  These are evaluated in order from the first to the last, and the final expression is what is stored as the cell value.

``````=LET( cleaned_up_first_name, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-","")," ",""), ".",""),
cleaned_up_surname, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"-","")," ",""), ".",""),
combined, LEFT(cleaned_up_first_name,1) & cleaned_up_surname,
LEFT( LOWER(combined), 8)
)``````

(The extra spaces and line feeds (Alt + Enter) are not required, but make the formula easier to read.)