Category Archives: Excel UDF Functions

First Word Last Word

These 2 UDF’s will extract the first and last words using a formula in your worksheet. It is worth noting that during a data split such as this, the data you are working with may require a Trim() applied to it so any rogue trailing or leading spaces are removed, making the first word and […]

UDF Choose csv file from dialog box

This UDF opens up the dialog box to choose a file to import into an Excel woorkbook. Function GetFile() As String Dim filename__path As Variant filename__path = Application.GetOpenFilename(FileFilter:=”Csv (*.CSV), *.CSV”, Title:=”Select File To Be Opened”) If filename__path = False Then Exit Function GetFile = filename__path End Function

UDF Get the comments from a cell

Comments in Excel can be stored in a cell and their locations are identified by a small red triangle in the top right of a cell. These comments can contain lots of text, carriage returns etc and can be very useful for a user in their own spreadsheet or a user wishing to explain specific […]

Import csv file and export in batches of 1000

This recent project assigned to datanology’s VBA team required a regular import of a standard csv file that was created from a web extract which then needed to be split into separate csv files (with headers) with a maximum of 1,000 records or rows. There was also a requirement to alter the order of the […]

Splitting and Moving data with VBA

Splitting data can be a cumbersome and highly manual task, and could result in inaccurate data. Splitting data, moving it about and intelligently preparing large amounts of extracted data can be accomplished in seconds using a datanology VBA Solution. In the short example below (data sanitised under DPA) the extracted data which was originally 12,000 […]

Excel UDF Get the colour of the cell

Dealing with colours jn Excel became a lot more important when Office 2010 was released. Conditional formatting allowed for large ranges of slightly different shades of the same colour to be used in ranges which are not so easy to identify by sight. This UDF example returns the value of the colour in the cell. […]

Excel UDF Get Workbook name

In Excel there is no built in function to check and return a result for the workbooks name. Even though it may be in clear sight to a user at the top of the screen, there may be situations where the workbooks name is required to be referenced against, such as counting the number of […]

Excel UDF Get Worksheet name

In Excel there is no built in function to check and return a result for the worksheets name. Even though it may be in clear sight to a user at the bottom of the sheet on it’s tab, there may be situations where the worksheets name is required to be referenced against, such as counting […]

Excel UDF Identify email address

We carry out a lot of work on document and data cleansing, data capture and data blending. One of the most common request is about email addresses and how to automate grabbing just the email address from a string of text in Excel. Because an email address is a unique entity and also has a […]

Extract a specific length of numbers from a string

Recently we have been asked to provide a solution for a client who was faced with potentially thousands of cells in a range where only a specific reference number within an alphanumeric sequence was required to be extracted. This was a fixed length of six numbers but this sequence could be anywhere in the string. […]

UDF to locate first letter in string

In the process of data manipulation, data cleansing for instance it is a regular occurrence to identify a structure of the string in a cell for a postcode or telephone number for instance, or to facilitate splitting a cells contents up by numbers, letters etc. This example UDF is designed to be pasted into the […]

UDF Extract all comments from a cell

This UDF (User defined function) is designed to be pasted into a VB Module (ALT + F11) and will then become a normal everyday formula that you can use in Excel to get the comment text out of a particular cell. There may be occasions when for instance a small database has been designed and […]

Excel UDF to validate UK Postcode

As part of our postcode analysis and cleansing processes, an important task is to ensure that a given postcode is actually a valid UK format postcode. It is not easy to spot errors or potentially invalid postcodes when you have a large list of addresses, especially if you have used OCR scanning that may mis-recognise […]

the webdesigner group security shielf
the webdesigner group logo

Close Button

Web Page Design by

The Web Designer Group