Tips & Tricks 99 – Remove Middle Name
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("…
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…
Excel Quiz 15
Article 17 – 3D Formulas – Hidden Wonder of Excel
3D formulas are one of the hidden wonders of Excel and not many of us know about the secret of this. The purpose of this article is to unravel the mystery of 3D formulas in Excel. Suppose you have 4 worksheets called Quarter1, Quarter2, Quarter3, Quarter4 as below. And you want to find the total of…
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…
Challenge 19 – Make Comparative Horizontal Bar Graph
—- The worksheet for this challenge can be downloaded from Literacy Rates Graph —- Suppose, you have been given data for two categories as given below. The challenge before you is to make a chart like below. You can choose to post your answer in comments section. Note – Solution to this challenge will be published…
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…
Article 16 – Dependent Data List 02 – Creating Flexible / Dynamic Dependent Data List even First Drop Down is Flexible
— The Excel file related to this article can be downloaded from Dependent Drop Down List 02 — In Article 15, we saw that our Dependent Data list can grow / reduce vertically but we had fixed the horizontal range. Now, we will remove this restriction and our data list can grow / reduce vertically as…
Challenge 18 – Find the Longest Word in a List
— The worksheet related to this problem can be found at Longest Word — Suppose you have a list of words in A1 to A10. The challenge before you is to write a formula (preferably non-array) to find the longest word in the list. In case of ties, pick up the first longest word. The…