Excel Formula to Concatenate First/Middle name initals and Last name

I have a roster of about 300 names I need help with a formula that will take the first initial of the first name and middle name, and the entire last name from three different cells and convert it to 1 cell lowercase. If there is no middle name eliminate the space i.e. John David Smith, jdsmith. or John Smith jsmith. Also id this possible if the entire name is in a single cell

Almost there, I have got this far I need the characters to be all lower case and eliminate the space if there is not middle name.

=CONCATENATE(LEFT(A4,1),LEFT(B4,1))&C4

Answers


You can modify your Excel Worksheet Formula as:

=LOWER(CONCATENATE(LEFT(A4,1),IF((B4<>""),LEFT(B4,1),""),C4))

Hope this may help.


Probably not the most beautiful formula, but it works on your example (only 1 middle name) :

=IF((LEN(A1)-LEN(SUBSTITUTE(A1," ","")))=1,LOWER(LEFT(A1,1)&RIGHT(A1,(LEN(A1)-FIND(" ",A1)))),LOWER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)&RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))))

example_img


Need Your Help

Eclipse error: JRE is selected, but the path is invalid

java eclipse ant weblogic12c

I have a Java web application I work on using an Eclipse IDE. I'm using WebLogic 12c.

how to redirect to current directory with rewriterule

apache redirect mod-rewrite

How can I redirect to current directory with rewriterule.