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...
Read More
Unit 8 – Excel Custom Function (UDF)
Access Excel VBA day month year difference between two dates
This Access Excel tutorial explains how to find complete year difference, month difference and date difference between two dates.
You may also want to read:
Excel DATEDIF Function to calculate date difference
Access Excel VBA difference between two dates (year difference, month difference, day difference)
In Excel worksheet, there is a Function called DateDif that can calculate difference between two dates in complete year, month or day.
In VBA, there is no DateDif Function. There is ...
Read More
Access Excel add leading zero
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 ...
Read More
Excel VBA Function sum colored Cell count colored Cell
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...
Read More
Use Excel VBA to check if workbook is opened
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...
Read More
Access Excel Generate random HKID Hong Kong ID card number
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...
Read More
Excel generate random letter A to Z using RANDBETWEEN
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...
Read More
Excel count substring in string or text
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...
Read More
Excel VBA Rnd Function to generate random number
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...
Read More
Access Excel assign number for custom sorting sequence
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...
Read More