Forum Discussion

Arydiel's avatar
Arydiel
Copper Contributor
Jun 09, 2023
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,"-","") =SU...
excel
Formulas and Functions
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Jun 09, 2023

    Arydiel 

    It takes less than a second.

    =LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(LEFT(A1)&B1),"-","")," ",""),".",""),8)
SnowMan55's avatar
SnowMan55
Bronze Contributor
Jun 10, 2023

Arydiel 

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

 

Resources