Excel first date of month and last date of month

This Excel tutorial explains how to find first date of month, last date of month using Excel worksheet Function and Excel VBA Function. You may also want to read: Excel Use Date in IF Condition Excel first date of month To find first date of month in Excel, first we have to understand DATE Function, you can use it to combine the year, month and day to a date. Syntax of Date Function DATE( year, month, day ) Example Formula Result Explanation =Date(2012,1,1) 1/1/2012 Co...
More

Check Excel #N/A using ISNA Function

This Excel tutorial explains how to check Excel #N/A cells using worksheet formula ISNA and VBA Function. You may also want to read: Excel IFERROR Function Cause of Excel #N/A #N/A is usually caused by lookup related functions where a value cannot be found in lookup table. Lookup Functions include Vlookup, Match, HLookup, Lookup. Another common cause is that when you use an Add-In formula, and then send the file to someone who doesn't have that Add-In. The formula was fine at the time...
More

Excel add page number in footer

This Excel tutorial explains how to add page number in footer. E.g. add page number to all worksheets, add page numbers to specific worksheets. Excel add page number in footer to one worksheet Select the worksheet you want to add page number in footer Navigate to PAGE LAYOUT tab > Print Titles   Click on Header/Footer tab > select the desired Footer format > OK   To check the page number in print preview, Go to FILE > Print Make sure the Settings o...
More

Access Excel extract file name from file path

This Excel tutorial explains how to extract file name from file path using VBA and without VBA. Excel Access VBA extract percentage from text Access Excel VBA extract number from text or extract alphabet Excel Extract Time from Date Time or Extract Date Excel extract file name from file path Assume that you have a file path such as C:\Program Files\Google\GoogleToolbarNotifier\a.jpg If you want to extract file name a.jpg from the path in Excel, first we need to think about how to de...
More

Excel insert file with and without VBA

This Excel tutorial explains how to insert file (insert object) in Excel using VBA and without using VBA. Excel insert file object (non-VBA) In Excel, insert file is known as insert object, there are two kinds of insert: Embedded Object - insert the source file into Excel Linked Object - insert the link of file into Excel, not the actual source file In terms of display of the inserted file, there are also two kinds: Display file with an icon, double click on the icon to open th...
More

Excel Delete Blank Rows

This Excel tutorial explains how to delete blank rows in Excel with VBA and without VBA. You may also want to read: Excel delete blank rows and blank cells before import into Access Excel VBA reformat merged cells to row Fill Blank Rows or blank Cells Excel Delete Blank Rows Sometimes we have data that contains blank rows, one example is data with pivot table layout where data are grouped together and repeated items are blank. Previously I wrote a post about filling blank rows, ...
More

Excel Access VBA extract percentage from text

This Excel / Access tutorial explains how to extract percentage from text. Access Excel VBA extract number from text or extract alphabet Excel Extract Time from Date Time or Extract Date Access Excel extract file name from file path Excel Access VBA extract percentage from text Recently I got a report that contains percentage but it is mixed with other text. For example, Salary increased by 10% We are unable to do any calculation unless we extract the 10% or 10 out from the text. ...
More

Excel VBA separate line break data into different rows

This Excel VBA tutorial explains how to separate line break data into different rows. You may also want to read: Access Excel remove line break and carriage return Excel VBA separate line break data into different rows Recently I received a report from a HR system which is claimed to be a standard report. In the report, some data are grouped into one Cell and are separated by line break. I found this report format totally unacceptable. From the database perspective, each dependent...
More

Excel VBA hide worksheet with password (xlVeryHidden)

This Excel VBA tutorial explains how to hide worksheet with password (xlVeryHidden). You may also want to read: Excel VBA protect worksheet with password Excel VBA Add password to Excel workbook in folder Protect VBA code with password Different kinds of workbook protection There are several kinds of protection in Excel. Add password to workbook. Users are asked to open the Excel file with password. Protect workbook with password. Protect the structure of all worksheets (...
More

Access Excel remove line break and carriage return

This Access / Excel tutorial explains how to remove carriage return and remove line break in a text. You may also want to read: Excel VBA separate line break data into different rows Access Excel remove carriage return and remove line break Recently our company uploaded employee data in spreadsheet data to the new system. One user reported that her home address shows some special code (something like hex code) that is supposed to be a space. Since the code appears after a comma, I suspec...
More