Sunday, May 9, 2021
Home Tags Function

Tag: function

Split Function

The Split function is a VBA function that is used for breaking strings into an array. The separation of string is identified by indicating a character or set of characters which are called delimiters. This function comes in handy, for example, when you have a list of whole names of people in a column of your worksheet and you want to split them in order to get the first and last names of each person.

Generate a List of Random Numbers

Sometimes a list of random numbers is required for temporary passwords, pin codes, or just dummy data for your database application. This can easily be produced without having to key in multiple entries. In the sample we use the RANDBETWEEEN function which returns a random number between the ranges you provide in the function's arguments. You can follow the simple example below that will give a 5-digit random number.

Calculate Number of Days in another Date Range

A custom function has been developed to calculate the Number of Days that one Date Range is within another. This can be very useful when calculating the overlapping periods.

Retain Trailing Zeros in a Text String

For this request we needed to ensure that a trailing zero was always displayed for a calculated text string. Through the use of an additional function the string included trailing zero's

Extract Text from String – using MID Function

For this problem we received a sample workbook. We then used VBA to create a custom function to extract the strings from the starting value and display in a separate column. This function can be re-used for many other types of extract and display

Text not Converting to Date

For this problem we received a copy of the workbook. We then used a formula to convert the original birth date text into a "DateValue" and finally format those cells as dates to create a column with the actual Birth Dates.

How to use the DateAdd function

To solve this problem we will use one of Excel's built-in functions DateAdd. This function provides the ability to Add and Subtract values from a given date. In this request we need to Add 4 years and 1 day to the date in a particular cell. So lets say we start with a spreadsheet that has the following dates that need to have 4 years and 1 day added to them.

Search for earliest date in Excel list using vLookup

The solution to this problem can be approached in a number of ways. In this post we will describe and method that uses VLOOKUP a built-in Excel function to derive the result.

How to use the Proper function to re-format Names

Excel's built-in functions can be used to solve the problem of unformatted Names and Addresses. The function we will use to solve this problem is the Proper function. It automatically capitalizes the first character in a name or address or any string.