Functions

If you are planning to step up your proficiency in Excel, you should learn how to use the look up functions. These functions are commonly used on tables that act like a database. While databases have SQL queries, which is challenging especially for non-programmers, Excel has these simple look up functions and the most widely used are VLOOKUP, HLOOKUP and LOOKUP.

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.

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.

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.

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

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

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.

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.

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.

In many cases when working with Excel the we use dates to calculate formulas or display a report. The ability to use the built-in Excel functions that relate to dates and to extend them is a fundamental skill in Excel. This post is the first in a series that will describe the basic use of key functions in Excel for beginners.