vendredi 4 décembre 2020

IF function with several complexities

If the value is < 50 million, I need to display the first 3 characters of the last name. If the value is > 50 million, I need to display the first 4 characters of the first name.

So far I have this formula which is working but I don't know how to nest the LEFT function to display only the first 4 characters of the first name.

Can you help?

A1 = Last Name, First Name
A1 Example = Smith John

Formula:

=IF(B1<50000000,LEFT(A1,3), TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), 1*LEN(A1)+1, LEN(A1))))

Aucun commentaire:

Enregistrer un commentaire