Forum Discussion
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!
It takes less than a second.
=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(LEFT(A1)&B1),"-","")," ",""),".",""),8)
5 Replies
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.)
It takes less than a second.
=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(LEFT(A1)&B1),"-","")," ",""),".",""),8)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.
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.
