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

More
# Unit 8 – Excel Custom Function (UDF)

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

More
# Excel get formula of a cell using FormulaText Function and VBA

This Excel tutorial explains how to show / get formula of a cell using FormulaText Function and VBA.
Search formula of a Cell
Assume that you have the below spreadsheet and you don't know whether any cells contain a formula.
Press CTRL + F to open Find and Replace dialog
In the Find what text box, enter equal sign = , then press Find Next button
Note that if a cell contains equal sign in Cell Text, it will also be searched.
Show formula of a Cell
Navigate to Formul...

More
# Get Foreign Exchange Rate in Excel (VBA and non-VBA)

This Excel tutorial explains how to get foreign exchange rate in Excel using VBA and non-VBA.
You may also want to read:
Get exchange rate using VBA Access Function
Excel currency converter template
Foreign Exchange Rate Websites
Excel allows you to create an external connection to website where you can get the data directly in the spreadsheet.
To begin with, look for a dynamic website that contains foreign exchange rate where the database constantly refreshes.
One example is ...

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

More
# Excel VBA LinkInfo Method

This Excel VBA tutorial explains VBA LinkInfo Method to check status of linked workbook.
You may also want to read:
Excel find all external links and broken links in workbook
Excel VBA LinkInfo Method
Workbook LinkInfo Method is usually used to check status of linked workbook, so that we know whether the workbook link is broken or normal.
Usually this Method is used with LinkSources Method.
Syntax of LinkInfo
Workbook.LinkInfo(Name, LinkInfo, Type, EditionRef)
Name
Required...

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 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 10% or 10 out from the text.
...

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

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

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

More