Suppose, you have been given a name in A1 = Roberto Albert Carlos and you need to remove the middle name. Hence, you want to have an answer Roberto Carlos here. Formula Way – The formula for the above case would be =IF(COUNTIF(A1,"* * *"),LEFT(A1,FIND(" ",A1&" "))&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),"") OR =IF(COUNTIF(A1,"* * *"),REPLACE(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND("…
Category: Tips and Tricks
Tips & Tricks 98 – Extract Middle Name
Suppose, you have been given a name in A1 = Roberto Albert Carlos and you need to extract the middle name. We have already talked about extracting first name and last name in following Tips and Tricks. We also talked about extracting initial of a middle name. Tips & Tricks 22 – Extract First Name…
Tips & Tricks 97 – Force Text to Columns Wizard to Appear for CSV Files
You have a csv file and 1. You double click it, the file will be opened directly in Excel. 2. You opened the file from File > Open but still the file opens directly in Excel. The solution is to change the extension of file from csv to txt. Hence, if you are having a…
Tips & Tricks 96 – Abbreviate Given Names
If you have names given like – Smith Johnson Liz lotte Christy tiara Lewisk John And you need to produce abbreviations or acronyms for them like below in all capitals Smith Johnson – SJ Liz lotte – LT Christy tiara Lewisk – CTL john – J Then you can use following formula for the same…
Tips & Tricks 95 – Send Excel Workbook / Worksheet as Attachment
You can very easily send an Excel / Workbook as an attachment directly from Excel. To send Excel Workbook To use this functionality, your workbook need not be saved. When you use a step from below, it will attach that point of time copy of Excel workbook in the mail. File > Share > E…
Tips & Tricks 94 – Select Big Range Very Easily
You have a need to select A1 to A10. This is a very easy job to do. Suppose, you need to select A1 to A10000. Now, it becomes more cumbersome. Suppose you need to select E23 to AB4325. It is all the more cumbersome job for you. Approach 1 – Put the range in Name…
Tips & Tricks 93 – Sorting from Left to Right
Generally, we do sorting in a column from top to bottom. But, what happens if the data is in rows. Then you will have to sort from left to right. This is possible in Excel. 1. Select your data in the row. 2. Data tab> Sort OR Home tab > Sort & Filter OR ALT+DS…
Tips & Tricks 92 – Financial Function – Calculate Effective Interest
You are applying for a loan and an interest rate has been quoted. The interest rate which is quoted is called "Nominal Interest Rate". They will quote Nominal Interest Rate in yearly terms. Hence, if they quote 12% interest for a loan, this is yearly figure. Now, you generally pay EMIs every month. They simply…
Tips & Tricks 91 – Financial Function – Calculate Compounded Interest
As part of our Mathematics courses in our childhood, we had learned about Compounded Interest. The famous formula which we remember is Compounded Balance = Principal x (1+rate)^N
Tips & Tricks 90 – Financial Function – Calculate Interest Rate
You want to take a loan. You know how much loan to take (pmt), you know how many months you want to pay up (nper) and you want to know effective rate of interest. Excel makes it easy to do. RATE function is the answer for this.