Below is a function which will return True or False if a valid IP address is entered. A valid IP address is of the form nnn.nnn.nnn.nnn where nnn >=0 and <=255 You can put following type of construct in a cell =IsValidIP(A2)
Category: VBA
VBA – Macro to Clean Non-printable characters
Sometimes, your data contains some characters which make some portion of your data unusable. Below is a macro which cleans your worksheet in the following way – It will remove non-printable characters with ASCII codes 0 to 31. It will remove leading and trailing blanks. Will remove characters with ASCII codes 127, 129, 141, 143,…
VBA – Macro to Combine (Append) Sheets
Many times, we need to combine worksheets together. Below is a Macro to do this. You just need to change the parameter in "Change Parameters in this Section". In case of many sheets, it will merge all sheets. If you don't want to merge all sheets but few sheets, just create two sheets named "Start"…
Downloads 18 – Sample CSV Files / Data Sets for Testing (till 5 Million Records) – Sales
Disclaimer – The datasets are generated through random logic in VBA. These are not real sales data and should not be used for any other purpose other than testing. Other data sets – Human Resources Credit Card Bank Transactions HR Analytics Note – I have been approached for the permission to use data…
Downloads 17 – Sample CSV Files / Data Sets for Testing (5 Million Records) – Credit Card
Disclaimer – The datasets are generated through random logic in VBA. These are not real credit card data and should not be used for any other purpose other than testing. Other data sets – Human Resources Sales Bank Transactions HR Analytics Note – I have been approached for the permission to use data set…
Downloads 16 – Sample CSV Files / Data Sets for Testing – Human Resources (5 million records)
Disclaimer – The datasets are generated through random logic in VBA. These are not real human resource data and should not be used for any other purpose other than testing. Other data sets – Sales Credit Card Bank Transactions HR Analytics Note – I have been approached for the…
VBA – Data Masking or Anonymize the Data through a Macro
Sometimes, you may need to sanitize your data before sharing your file. The below macro would sanitize the data and since this is completely based on random generation, hence, it can not be recreated. Make sure that you have a copy of the Excel sheet before you sanitize or mask or anonymize the data by…
VBA – A Function to Check whether a Given Number is Prime
Below is an optimized function to check on the primality of a number. This function takes input number as Double, hence can be used to check upto a number having 15 significant digits. Whereas Long can take up to a maximum of 10 significant digits and maximum number it can support is 2,147,483,648. First function…
Article 45 – How to know if a Cell contains Time
We talked about Article 19 – How to Check if a cell contains a date. Now, in this article, I will talk about checking the cell for time. The way Excel stores dates as numbers, in the same way it stores time as numbers only. Only change is in range. The numbers get stored between…
Tips & Tricks 154 – Insert a Space after Each Character
You have a sheet and you want to put a space after each character. For example, A1 contains 12345, you want to make it 1 2 3 4 5. B1 contains Mango12 and you want to make it M a n g o 1 2. The same need to be accomplished by a simple macro….