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 […]
Category Archives: Excel UDF Functions
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
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 […]
Current Projects, Excel UDF Functions, VBA Examples and reference
Import csv file and export in batches of 1000
17
Nov
Nov
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 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 […]
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. […]
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 […]
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 […]
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 […]
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. […]
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 […]
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 […]
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 […]
