HAN.xlsx [Read-Only] - Excel at Microsoft Excel!
Home
Introduction
Excel
101
So, you're new to spreadsheet huh.. Check this out!
Excel
Formula
How to write an excel formula!
New Posts
Love
Meter
Who is Your Crush's Big Time Crush?
Unprotect
Sheet
Remove password from protected worksheets and workbooks!
Dictionary
Attack
Open password-protected excel file using a dictionary attack!
Projects
Gag
Love
Meter
Who is Your Crush's Big Time Crush?
Other
Gag
Coming soon!
Social Network
Excel
Facebook
Coming soon!
Excel
Twitter
Coming soon!
Games
Formulas
Financial Modeling
Developer
Passwords
Password Recovery
Unprotect
Sheet
Remove password from protected worksheets and workbooks!
Dictionary
Attack
Open password-protected excel file using a dictionary attack!
Brute-force
Attack
Learn how to open password protected Excel File! Coming soon!
About
HANxlsx
About
Me
A little intro..
Privacy
Policy
Privacy Policy!
Disclaimer
Disclaimer!

[back to top!]

Coming up next!

Chatting and updating status using excel spreadsheet like a boss!

Our next excel project is to create a facebook-like excel spreadsheet that works like facebook. You don't say.. :D

Stay tuned, people! More cool stuff is coming!

Thursday, November 8, 2012

Get Initial from a Name - Because Full Name is Too Mainstream


Let’s say you have a list of Names, and you want the initials of the people. There are many ways to do it. The easiest one is to do it manually. You can just type in the initials to the column next to the Names. But it would take million years if you have trillion of names. Ok, I know I exaggerated things.

I will try to cover this topic in VBA section later. Before it happens, let’s see how to do it using simple excel formula.





So this is our list:


OMG, who made this list?

Here is how to use excel formula to get their initials, just copy the following formula to cell B2:B8. hahaha..

=IF((IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1))=1,UPPER(LEFT(TRIM(A2),1)&RIGHT(TRIM(A2),1)),IF((IF(LEN(TRIM(TRIM(A2)))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)=2),UPPER(LEFT(TRIM(A2),1)&MID(TRIM(A2),FIND(" ",TRIM(A2),1)+1,1)),IF((IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)=3),UPPER(LEFT(TRIM(A2),1)&MID(TRIM(A2),FIND(" ",TRIM(A2),1)+1,1)&MID(TRIM(A2),FIND(" ",TRIM(A2),FIND(" ",TRIM(A2),1)+1)+1,1)),IF((IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)=4),UPPER(LEFT(TRIM(A2),1)&MID(TRIM(A2),FIND(" ",TRIM(A2),1)+1,1)&MID(TRIM(A2),FIND(" ",TRIM(A2),FIND(" ",TRIM(A2),1)+1)+1,1))&MID(TRIM(A2),FIND(" ",TRIM(A2),(FIND(" ",TRIM(A2),FIND(" ",TRIM(A2),1)+1)+1))+1,1),":O"))))



I put IF, LEN, TRIM, UPPER, SUBSTITUDE, MID, and FIND to cook-up this formula. I will update this post later to explain the formula.