Access Excel add leading zero

Posted on May 5, 2015December 26, 2016 by Wyman W

This Access / Excel tutorial explains how to add leading zero using Text Function or using custom Function to pad other text in suffix or prefix. Excel add leading zero (non-VBA) Number in Excel can come in two forms - Text or Number. For number in Number format, you may not add zero in prefix For number in Text format, you can add a zero in prefix In order to convert a Number to Text, add a ' in the prefix. For example, instead of typing 00123, type '00123 Sometimes you receive ...

Excel VBA Function sum colored Cell count colored Cell

Posted on April 10, 2015 by Wyman W

This Excel tutorial explains how to sum colored Cell and count colored Cell in Excel worksheet. Excel VBA Function sum colored Cell and count colored cell In my previous posts, I have explained how to use ColorIndex Property to find the first colored Cell. In this Post I will create a custom Function to count colored Cell of a Range and sum colored Cell To recap the previous posts, you can click on the followings. Excel VBA custom Function Find the first colored Cell value VBA Excel...

Use Excel VBA to check if workbook is opened

Posted on April 9, 2015March 10, 2016 by Wyman W

This Excel tutorial explains how to use Workbooks Open Method to open a closed workbook and check if workbook is opened. Excel VBA Workbooks Open Method In worksheet automation, we may need to programmatically open another workbook to change data based on the active workbook. Workbooks Open Method is very straight forward to use, you just need to specify the file name to open, but there are many other optional parameters you may want to use. Note that Workbooks Open Method is different from...

Access Excel Generate random HKID Hong Kong ID card number

Posted on April 5, 2015March 17, 2016 by Wyman W

This Access Excel VBA tutorial demonstrates how to generate random HKID / Hong Kong ID card number. You may also want to read: Access Excel Function HKID Check Digit (last digit) Excel VBA Rnd Function to generate random number Excel generate random letter A to Z using RANDBETWEEN Assign unique random number using Excel Access Excel Generate random HKID (Hong Kong ID card number) The purpose of this article is to generate a random HKID (Hong Kong ID card number) that has va...

Excel generate random letter A to Z using RANDBETWEEN

Posted on April 5, 2015March 20, 2016 by Wyman W

This Excel tutorial explains how to generate random letter A to Z / a to z or a mix using RANDBETWEEN Function. You may also want to read Assign unique random number using Excel Use VBA to generate non-duplicated random number Excel generate random letter A to Z using RANDBETWEEN In order to generate random letter, we have to make use of two Functions. - RANDBETWEEN - CHAR In the below sections, I will briefly go going through these two Functions. For details, click on the link...

Excel count substring in string or text

Posted on March 29, 2015 by Wyman W

This Excel tutorial explains how to count substring in string (text). Excel count substring in string (text) Excel does not have a buit-in Function to count substring in string (text), instead you need to work around to get the job done. Alternatively, I have made a Macro solution for your reference. Excel count substring in string using Len and Substitute Function Excel Len Function is to measure the length of text. Excel Substitute Function is to replace a substring in a text. To c...

Excel VBA Rnd Function to generate random number

Posted on March 27, 2015March 17, 2016 by Wyman W

This Excel tutorial explains how to use Excel VBA Rnd Function to generate random number and create custom Function for random decimal number. You may also want to read: Excel RAND RANDBETWEEN Function to generate random number Access Excel Generate random HKID Hong Kong ID card number Assign unique random number using Excel Excel VBA Rnd Function to generate random number Rnd Function is for use in Access, Access VBA, Excel VBA, it is used to generate random number larger than 0 and s...

Access Excel assign number for custom sorting sequence

Posted on March 26, 2015April 20, 2015 by Wyman W

This Access Excel tutorial explains how to assign number to Text for custom sorting sequence and how to use Excel Custom Lists. You may also want to read: Sort data using custom Function by Bubble Sort Sort data using Range Sort Method Sort data using Table AutoFilter Sort data using Worksheet AutoFilter Access Excel Assign number to Text for custom sorting sequence Suppose your company has departments IT, HR, Finance. Normally if you sort the sequence in ascending order, it wou...

Access Excel VBA group number with larger than smaller than (age group)

Posted on March 26, 2015December 4, 2015 by Wyman W

This Access Excel tutorial explains how to create custom Function to categorize /group number with larger than smaller than symbol, such as age group. Access Excel categorize / group number with larger than smaller than symbol Sometimes when you have a group of numbers, you may need to categorize / group them into different categories for analysis. For example, you may need to make age group for employees, such as <20 >=20 to <30 >=30 to <40 >=40 In order ...

Excel convert column letter to column number vise versa

Posted on March 26, 2015March 26, 2015 by Wyman W

This tutorial explains how to convert column number to column letter, and convert column letter to column number in Excel. Excel column letter to column number / column number to column letter conversion Column number refers to the column expressed in integer, while column letter refers to column expressed in alphabet. For example, for Cell D1, column number is 4, while column letter is D. Excel convert column number to column letter To convert column number to column letter, make use o...