Convert column number to column letter
Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]
Search for a file in folder and subfolders (VBA)
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
How to use the SPLIT function [VBA]
The picture above shows a user-defined function (UDF) that splits the string in cell B3 using delimiting character "|". The SPLIT function […]
The "Text to columns" feature in excel lets you split multiple values in a single cell using a delimiting character, […]
Substitute multiple text strings [UDF]
The user defined function demonstrated below replaces multiple old text strings with new text strings in a cell. The custom […]
Lookup multiple values in one cell [UDF]
Chrisa asks: Hi Oscar...this is a very interesting function and helped me a lot so far. My file though is […]
Learn how to return values depending on how you enter the UDF
In this vba tutorial I am going to show you how to return values from an udf, depending on where […]
Extract unique distinct values from a filtered table [udf and array formula]
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]
Sean asks: Sheet1 A B C D 8 Country Europe 9 Lights 100 10 Type A 200 11 12 Country […]
Two-way lookup using multiple tables [UDF]
This post describes how to lookup values in two dimensions with multiple tables using a User Defined Function. You can […]
Sumif across multiple sheets [UDF]
This post describes a basic user defined function that searches multiple ranges and adds corresponding values. […]
Import historical stock prices from yahoo [UDF]
Here is how to quickly import historical stock quotes from yahoo. First you need to know the company ticker. Go […]
List files in a folder and subfolders [UDF]
This blog post describes how to list files in a folder and subfolders using vba. Where to copy vba code? […]
Filter unique distinct records (case sensitive)
The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters. How to use the […]
Filter unique distinct values (case sensitive) [Excel UDF]
The User Defined Function demonstrated in the above picture extracts unique distinct values also considering lower and upper case letters. […]
This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]
In this post I will describe a basic user defined function with better search functionality than the array formula in […]
Filter emails from an excel range [UDF]
This udf extracts all words containing a specified string. Example, Cell range B1:M50 contains random sentences. I have inserted some […]
Find positive and negative amounts that net to zero [UDF]
This article describes a UDF that finds positive and negative numbers that net to approximately zero. You specify the range […]
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
List permutations without repetition [UDF]
This blog post describes how to create permutations. Repetition is NOT allowed. VBA code: How to implement user defined function in […]
List permutations with repetition [UDF]
This blog post describes how to create permutations. Repetition is allowed. Vba code: Function ListPermut(num As Integer) 'Permutations with repetition […]
Filter values existing only in one out of two ranges [UDF]
This post describes how to filter values existing only in one out of two cell ranges. See picture. This udf […]
Filter values in common between two cell ranges [UDF]
I tried the array formula in this post: Filter common values between two ranges using array formula in excel to […]
Filter duplicates in a large dataset [UDF]
Overview This blog post shows you how to filter duplicate values in a large list. We are going to create […]
Count unique distinct values in a large dataset [UDF]
Overview This blog post describes how to count unique distinct values in list. We have created excel formulas before to […]
Remove duplicates from a large dataset [UDF]
Aamer asks: I have a sheet with 3000 rows of invoice dates that are out of order. There could be […]
Filter unique words from a range [UDF]
Overview This blog post describes how to create a list of unique words from a cell range. Unique words are […]
Filter unique distinct words from a cell range [UDF]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
Filter duplicate words from a cell range [UDF]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
Split words in a cell range into a cell each [UDF]
This post describes how to split words in a cell range into a cell each using a custom function. I […]
Welcome! I am 