The following is the generic formula to extract the first name from a full name. =LEFT(“Full Name”,SEARCH(” “,”Full Name”)-1) Watch the short video: Extract First Name using TEXTBEFORE function in Excel Explanation SEARCH function in the formula will return the position of ” ” (Space character) from the Full name. In the formula used in … Read more
To show the current sheet’s name in a cell, for a report title or a header, use CELL(“filename”) and take everything after the closing bracket. In Excel 365 it’s a short TEXTAFTER formula. Excel 365TEXTAFTER + CELL Any versionMID + FIND + CELL NeedsThe workbook to be saved Watch the short video: Formula to get … Read more
Instead of adding VLOOKUP columns to bring region or category into a sales table, connect the tables with a relationship. A PivotTable can then use fields from all of them at once, with no extra formulas. WhereData → Relationships PivotTableUse this workbook's Data Model Works inExcel 2013 and later for Windows Video The problem Sales_Data … Read more
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 … Read more
To place room names, chainages or labels at known coordinates, build a -TEXT command for each row in Excel and paste the column into AutoCAD. Hundreds of text objects appear in seconds. Command-TEXT Formula=”-TEXT “&A2&”,”&B2&” 10 0 “&C2 Works inAny AutoCAD version The data The text to place is in column C, with its X … Read more
STOCKHISTORY pulls historical share prices and currency rates straight into Excel: daily, weekly or monthly, for NASDAQ, NSE, BSE and other exchanges. One formula returns the whole table, and it can be refreshed any time. Works inMicrosoft 365 (Windows, Mac, web). Not in Excel 2021 or earlier ReturnsA spilled table of dates and prices NeedsAn … Read more
New to Excel, or teaching someone who is? These are the terms you'll meet in every tutorial, from workbook and cell reference to spill range and Power Query, each in one or two plain sentences. Terms40 LevelBeginner to intermediate Applies toExcel for Windows, Mac and the web The basics Term Meaning Workbook An Excel file … Read more
Every Excel error code tells you what went wrong: #DIV/0! is a division by zero, #N/A is a value that wasn’t found, #REF! is a reference that no longer exists. Here is what each error means, a typical cause, and how to fix it. Most common#N/A, #DIV/0!, #VALUE!, #REF! Newer errors#SPILL!, #CALC!, #FIELD!, #BUSY! Find … Read more
How to import Facebook Friends List to Excel is one of my most watched YouTube videos. In this blog post I will explain this technique step by step, for those whose prefer reading over video lectures. Facebook has an information section, where we can View or Download all those data related to our Facebook Account. … Read more
Power Query normally hard-codes values such as a year or a row count into its steps. Point those steps at a cell instead, and anyone can change the cell and click Refresh All to get new results, without opening the editor. TechniqueTable → Drill Down → use in M code ExampleTop N films of any … Read more