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 m...

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 i...

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 c...

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 grou...

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. ...

This tutorial explains difference between ByRef ByVal in Access Excel VBA. You may also want to read: Difference between Dim and Set Excel VBA Option Explicit to force declaration of variables Function, Sub, Method, Property Differences Difference between ByRef ByVal in Access Excel VBA ByRef means to pass a variable or Object by Reference (memory address). ByVal means to pass a var...

This tutorial explains how to write a custom VBA Excel Function to vlookup second matched value or specific occurrence. You may also want to read: Case sensitive lookup Excel Lookup Function lookup multiple criteria not in the first column Excel lookup partial text using Vlookup with Wildcard Excel lookup Function lookup multiple criteria Excel Vlookup second matched value or speci...

This tutorial explains how to use Excel Indirect Function to convert text to Reference, and how to use in Data Validation. Excel Indirect Function Excel Indirect Function is to convert text to Reference (or cell value). For example, formula =A1 returns A1 value because A1 is considered as Reference, but ="A1" is considered as Text. Excel Indirect Function can convert the "A1" Text to become Refe...

This Excel tutorial explains how to use Excel Address Function and how to convert column number to column letter. Excel Address Function Excel Address Function is used to combine column number and row number to return Cell address in Text format. Excel Address Function is most useful is converting column number to column letter, or use with Indirect Function to convert the Text address to Ran...

This tutorial explains how to send email in Excel VBA using CDO and olMailItem. Send Email in Excel VBA using CDO and OlMailItem There are mainly two methods of sending email in Excel VBA. The first method is to use CDO Object, another is to use SendObject Method, I will explain each method in the below sections. Send Email in Excel VBA using olMailItem Object The first method uses olMailItem,...