Excel Functions

This category holds all the posts which are related to Functions which are very useful while writing Macros.

Excel UDF: How to Get All words in Reverse Order from a String

I created this UDF (User defined Function) to help you in arranging all the Words of a String in Reverse Order. It will put the Words in a reverse Order. Letters of each words will remain in same (proper) order. If you want to reverse the whole String then refer this Article. Put the below code inside your Regular Module. [...]

Excel UDF: To exctract All Numbers, Special Characters and Alphabets from a String

Sometimes you require to Extract All Numbers and Characters from a mixed String. I have written a UDF (User Defined Function) to extract them. UDF Function to Extract Numbers from a String UDF Function to Extract Special Characters from a String UDF Function to Extract Alhpabets from a String Add the below Code in any of your Regular Module of [...]

How to compare two columns in Excel to find duplicates

Many a times we require to compare two columns data and find out all the Duplicates. So in this article you are going to see how we can compare two columns in Excel and find out duplicates. There are two ways of comparing two columns in an Excel Worksheet. 1.By Using Excel Formula 2. By Using Excel Macro Compare two [...]

Strip HTML : How to Remove HTML tags from a String in VBA

In this article I am going to write an User Defined Function (UDF) which is used to remove all HTMLs from a String Stored in Cell. It can be used as normal Inbuilt Formulas of Excel. For writing this function i have used VBScript Regular Expression to Replace all the HTML tags with BLANK "". In code you can see [...]

COUNT and COUNTA Function

COUNT(): COUNT is a Excel inbuilt formula. It takes CELL RANGE as an input and give the total count of the Cells which are having Numeric Values. Important: COUNT counts only those cells which are having "Numeric" Values. It ignores those cell which are having non-numeric values. Syntax: =COUNT(Cell Range) Input Parameter: Cell or Cell Range ("A1:B2") or ("A1") Output: [...]

By |Categories: Excel Functions|0 Comments

How to Create Drop Down List in Excel

Drop down list is made in Excel Cell to limit the Entry in to a Cell from a predefined set of values. It means you are restricted to select one value out of set of Predefined values. On creating a drop down list on a cell or cell-range, one drop-down will be displayed with set of predefined values, on selecting [...]

File Name Formula

There are few instances when we want to store the File Name or Current Workbook Name in a particular Cell. This is very easy. This can be done by using Cell() function as shown below: Syntax: =CELL("filename") Returned Value: C:\Users\Vish\Desktop\[Test.xlsx]Sheet1 Note: The problem with this is that it gives the complete path including Drive and folders with Sheet Name. To [...]

Hide Horizontal or Vertical Scrollbar in Excel Workbook

In one of the Article, i had shown how to hide or display the Horizontal and Vertical Scroll Bar of an Excel Workbook. Now here I am going to show how can we DISPLAY or HIDE, Horizontal or Vertical Scroll Bar in an Excel Workbook. Excel 2003 || Excel 2007 Excel 2003 In Excel 2003, follow below steps to hide [...]

Restrict to fixed text or integer length in the given range of cells

Follow below steps to Restrict to fixed text or integer length in the given range of cells: 1. Select the Cell Range where you want to put the restriction 2. Go to "Data" Tab in Excel   3. Click on Data Validation Option and Select "Text Length" from the Drop down as show below:   3. Select the Data option [...]

Filter by Cell Color in Excel

In Excel 2007/2010, it is very easy to filter rows by Cell Color. In Excel 2007/2010 we have inbuilt functionality to do so. There can be two way to do this Filter by Color. Follow the below Steps to Filter by Cell Color in Excel 2007/2010: 1. Select the Range where you want to apply the Filter. […]

Page 4 of 512345