The following is the generic formula to extract the Last name from a full name, provided the name contains only First and Last Names. =RIGHT(“Full Name”,LEN(“Full Name”)-SEARCH(” “,”Full Name”)) Explanation SEARCH function in the formula will return the position of ” ” (Space character) from the Full name. In the formula used in the cell…
The following is the generic formula to extract the first name from a full name. =LEFT(“Full Name”,SEARCH(” “,”Full Name”)-1) Explanation SEARCH function in the formula will return the position of ” ” (Space character) from the Full name. In the formula used in the cell C3, SEARCH function will return the position of ” ”…
A formula using the combination of CELL, FIND and MID functions in Excel can be used to return the Sheet Name into a cell. Formula to return Sheet name The following is the generic formula to return Sheet name in Excel. =MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,255) Explanation CELL function with “filename” as it’s first argument will return the file…
Formula using TEXT function TEXT function in Excel can be used to get the Day Name (Sunday, Monday, etc.,) from a valid Excel Date. When the date ‘26/08/2019‘ is used inside TEXT function with “DDDD” as it’s second argument the formula will return ‘Monday’, the weekday corresponding to that date. =TEXT(“26-08-2019″,”DDDD”) “DDDD” will return the…
How to build Relationships between Excel Tables using the Data Model of Excel is explained in this Tutorial. Purpose of connecting Tables VLOOKUP and INDEX+MATCH formulas always get the job done, but a lot of them will essentially slow down the workbook. Moreover, the Lookup and Reference formulas also demands maintenance with addition or deletion…
A User Defined Function (UDF) which can split data into multiple Rows or Columns on the basis of the specified delimiter in Excel is explained in this blog post. Watch the video on Excel Formula to split data into Columns or Rows Text to Columns feature is the most commonly used Tool to split data…
In this blog post, I will explain about an ‘Excel formula’ that can be used to ‘Import Text from Excel to AutoCAD’. Suppose you have some data like the following that needs to be inserted into AutoCAD. The texts to be inserted into AutoCAD are in the column C and the X,Y Co-ordinates of the…
In the second week of June 2020, Microsoft introduced a new Excel Function called STOCKHISTORY, with which we can pull the Historical data of Stocks Prices and Currency exchange rates into an Excel worksheet. Note: At present, STOCKHISTORY function is available only to the 50% of Microsoft 365 Subscribers in the Beta level of the Office…
Following are the Basic Excel Terms and Terminologies Workbook – Refers to an an Excel Spreadsheet file. Worksheet – A collection of rectangular blocks called Cells organized in Rows and Columns. Cell – Building block of a Worksheet. The rectangular space where data is entered, stored and displayed. Column – Cells aligned Vertically Row –…
If you are a person who use Excel a lot, you might have encountered with these Error Codes like #DIV/0!, #NAME?, #N/A, etc. Each of these error codes tell us something. It gives us information on how to troubleshoot an incorrect formula. Following are the most common Error codes in Excel and this blog post…
