This Excel VBA tutorial explains how to convert date period to date row.
Excel VBA convert date period into date row
Different systems store date transactions differently. For example, if you apply for annual leave from Jan 1 to Jan 3, some system store the data in data base using date period
Begin Date
End Date
1/1/2018
1/3/2018
However some system store one date per row.
Begin Date
End Date
1/1/2018
1/1/2018
2/1/2018
2/1/2018
3/1/2018
1/3/2018
...
Read More
Data Conversion
Excel assign sequence number to duplicate records
This Excel tutorial explains how to assign sequence number to duplicate records using COUNTIF Function.
You may also want to read:
Excel check duplicate values using Conditional Formatting
Excel delete duplicated data in consecutive rows
Excel assign sequence number to duplicate records
Suppose you have two sales transactions on the same date but you don't have any transaction timestamp (but records are in ascending order of time), you may want to add a number to each transaction so tha...
Read More
Excel extract text in brackets
This Excel tutorial explains how to extract text in brackets or in defined text in Excel spreadsheet and VBA.
Extract text in brackets in Excel spreadsheet
In the previous post, I demonstrated how to remove text in brackets or in any defined text, in this post I will explain how to extract the text in brackets or in any defined text.
In Excel spreadsheet, you can extract any substring using Mid Function.
To recap the syntax of Mid Function
MID( text, start_position, number_of_characte...
Read More
Access Excel capitalize first letter in a sentence
This Access Excel tutorial explains how to capitalize first letter in a sentence using VBA custom Function.
Capitalize first letter in a sentence
In Excel spreadsheet, there is a Function called Proper, which converts all the first letter of each word to capital letter.
Example
Using Proper Function
he is a boy. she is a girl.
He Is A Boy. She Is A Girl.
he is a boy. his name is John. mary is a girl.
He Is A Boy. His Name Is John. Mary Is A Girl.
Mr. smith is happy
...
Read 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...
Read 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 extract text in brackets
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 1...
Read 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...
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
Access Excel custom split Function to delimit text
This tutorial shows how to create a custom Access Excel VBA custom Function to delimit text with the help of VBA "Split" Function instead of "Text to Columns".
Delimit text in Excel (non-VBA)
In Excel worksheet, there is a function called "Text to Columns" (Data > Text to Columns) to separate a text by delimiter. For example, Cell A1 contains "a,b,c" and after split by comma, A1 contains "a", B1 contains "b" and C1 contains "c".
An alternative way to do that is using Left Function with...
Read More