Excel Snippets

Excel VBA workbook for splitting email address data

This is a workbook I created with VBA code to resolve an issue where a contact list is exported, but the export puts multiple email address into one cell and separates them using a semicolon character.



Worksheet Before Example


This can happen where an export query in a CRM system uses the MS SQL STUFF() function.


For the email address list to be of use in a csv file for merging or email broadcast, each email address should be in its own row with applicable company contact data.


This code expands an email address list by putting each email address in its own row, but also retains the rest of the contact data. There is also basic cleaning functionality to remove excess spacing, remove Excel hyperlinks and convert email addresses to lower case. Rows with no email address are ignored, as these records would be of no use for emailing purposes.



Worksheet After Example


The workbook will work out how many rows of data are in use, so there is no need to amend formulae in the workbook with this information.


There is a small amount of test data in the workbook so that the functionality can be easily demonstrated on download, but you can safely remove this for personal use.


Simply paste your data into the 'Source' worksheet from cell A2 onwards. Your email addresses must reside in column A. Click the button in the top-left (cell A1) to run the process. Your processed data will then be found in the 'Output' worksheet.


Don't forget to enable macros when using the workbook

Custom accounting number formats

If you are producing worksheets for different people, they may have different preferences on accounting formats. Here are a few different variations which can be copy and pasted into Excel as a custom format:




Description:

Negative values in red - zero shown as hyphen:


Excel format code:

_-* #,##0.00_-;[Red]-* #,##0.00_-;_-* "-"??_-;_-@_-


Example:

Example 1

Description:

Include British pound symbol - negative values in red - zero shown as hyphen:


Excel format code:

_-£* #,##0.00_-;[Red]-£* #,##0.00_-;_-£* "-"??_-;_-@_-


Example:

Example 2

Description:

Include euro symbol - negative values in red - zero shown as hyphen:


Excel format code:

_-€* #,##0.00_-;[Red]-€* #,##0.00_-;_-€* "-"??_-;_-@_-


Example:

Example 3

Description:

Include dollar symbol - negative values in red - zero shown as hyphen:


Excel format code:

_-$* #,##0.00_-;[Red]-$* #,##0.00_-;_-$* "-"??_-;_-@_-


Example:

Example 4

Description:

Negative values in red - zero shown as hyphen - no decimal places(round):


Excel format code:

_-* #,##0_-;[Red]-* #,##0_-;_-* "-"??_-;_-@_-


Example:

Example 5

Description:

Negative values in red - zero shown as hyphen - no decimal places(round) - minus figures in parenthesis:


Excel format code:

_-* #,##0_-;[Red](#,##0)_-;_-* "-"??_-;_-@_-


Example:

Example 6

Custom percentage formats

Excel's built in percentage format will show 0% if the value is zero. This can look a little messy in a worksheet if it is designed to be populated over a period of time.

To show zero percentages as a hyphen you can use the following formats:




Description:

For two decimal places (if the value is not zero) - negatives in parenthesis:


Excel format code:

_-* #,##0.00%_-;(#,##0.00%);_-* "-"_-;_-@_-


Example:

Percentage Example 1

Description:

For zero decimal places (if the value is not zero) - negatives in parenthesis:


Excel format code:

_-* #,##0%_-;(#,##0%);_-* "-"_-;_-@_-


Example:

Percentage Example 2

Description:

For two decimal places (if the value is not zero) - negatives in parenthesis and red text:


Excel format code:

_-* #,##0.00%_-;[Red](#,##0.00%);_-* "-"_-;_-@_-


Example:

Percentage Example 3

Description:

For zero decimal places (if the value is not zero) - negatives in parenthesis and red text


Excel format code:

_-* #,##0%_-;[Red](#,##0%);_-* "-"_-;_-@_-


Example:

Percentage Example 4

Description:

For two decimal places (if the value is not zero) - negatives in red text:


Excel format code:

_-* #,##0.00%_-;[Red]-#,##0.00%;_-* "-"_-;_-@_-


Example:

Percentage Example 5

Description:

For zero decimal places (if the value is not zero) - negatives in red text:


Excel format code:

_-* #,##0%_-;[Red]-#,##0%;_-* "-"_-;_-@_-


Example:

Percentage Example 6

Excel VBA workbook for finding duplicated rows

This is a VBA workbook for identifying entire rows that are duplicated in a worksheet.


If the worksheet you are working on contains rows which have been duplicated in error, it can be tricky to find them. This can occur if you are working with imported data from another application and some of the data has been saved twice by mistake. This may be the result of a glitch when saving the data.


Excel does have functionality for finding duplicates, but this is unsuitable for matching entire rows.


There is a small amount of test data in the workbook so that the functionality can be easily demonstrated on download, but you can safely remove this for personal use.


Simply paste your data into the 'Sheet1' worksheet starting at cell A1. Click on the button in the Control worksheet. The data where rows have been duplicated will be written to a worksheet named 'Duplicates'. An extra column will be generated to the right of the found data which will contain the row number where the duplicate is located. The source data will remain untouched.


Don't forget to enable macros when using the workbook

Exporting to CSV - changing separator character

Some software products have issues with importing CSV data from Excel when commas are used within fields. To get around this issue, use the pipe symbol(|) as a separator when exporting to CSV. Excel itself doesn't allow you to change the separator character, so you will need to follow these instructions to achieve this aim:


Windows Instructions


Control panel > Region and Language > Additional settings(button) >

Click on 'List Separator' field and change entry to the pipe symbol '|'

Click 'Apply'

Click 'OK'


Now when you export your Excel worksheet to CSV format, the pipe symbol will be used as a separator character.


Don't forget to switch it back when you are finished!


Protect sensitive data from inquisitive colleagues

If you work with sensitive data such as payroll information and are in a shared office, there is a likelihood that someone may want to look over your shoulder while you are working to see this data.


Microsoft have a custom format that you can apply to cells to prevent data from being seen in a worksheet.

This format is comprised of three semi-colons, i.e.:


;;;


This will show blanks in the Excel worksheet to the casual snooper.


It should be noted that the values will still be shown in the formula bar, so that you are able to edit this information.

Suppression of '#N/A' in VLOOKUP/HLOOKUP

There are occasions where a worksheet that contains VLOOKUP or HLOOKUP formulae can look untidy, due to multiple '#N/A' messages appearing. This can be caused by data not being present yet in order for the formula to calculate properly.


The ISNA function can be combined with VLOOKUP/HLOOKUP to suppress the '#N/A' message that is generated when a value is not yet available. The example below uses a named range of 'range1':


=IF(ISNA(VLOOKUP(C4,range1,2,FALSE)),"",VLOOKUP(C4,range1,2,FALSE))


In order to save time in creating an ISNA version of a simple VLOOKUP/HLOOKUP formula, I have created a web form to automate the process. Click on the button below to use the tool:


Using INDEX/MATCH instead of VLOOKUP/HLOOKUP

Formulas INDEX and MATCH can be combined to produce results similar to VLOOKUP and HLOOKUP, but are more flexible in their operation. For example, VLOOKUP requires that the data that you are comparing is contained in the first column supplied. INDEX/MATCH is not constrained to this limitation. INDEX/MATCH also performs a little faster than VLOOKUP/HLOOKUP.


INDEX/MATCH can also allow for more than one value to be compared, so that data can be found on an X/Y axis.


It is recognised that INDEX/MATCH formula can be tricky to write due to the flexibility that they allow, especially if you want to combine them with an ISNA formula. To help with the process, I have created two web forms that can be used to automate the task. Click on the relevant button below:


Count number of cells that are not blank

The following formula will count cells that are not blank within the named range 'range1':


=COUNTIFS(range1,"<>"&"")


The ability to count cells that are not blank in a column can be useful for the purposes of error detection. An example could be if you are working with imported data and you wanted to find out if a data entry problem has occurred where a field was not populated, such as a contact name or company name.


To do this, you could count how many cells are not blank in a company name column and also count how many cells that are not blank in a contact name column. By comparing these two values, a difference would indicate that data is missing from one of the columns.


To make this error easy to spot, you could apply a cell rule to colour the comparison cell red if the value was not equal to zero.

Using TRIM to remove both space and &nbsp;

By default the TRIM function will not remove &nbsp; characters. To add it to the TRIM function use:


=TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)))


Note that &nbsp; characters could be present if you are copying and pasting data from an external source.

Suppressing #DIV/0! errors using IFERROR

If you have a formula that will return an error if the data is incorrect, you can suppress the error message with IFERROR.

For example, a formula dividing B3 by C3 would return an error of #DIV/0! if C3 contained zero.

The following formula would show zero in this scenario.


=IFERROR(B3/C3,0)


Creating a data form

Although data within companies is best recorded and stored using a database, for most staff, it is quicker to set up and requires less time and planning to record small datasets within an Excel worksheet. This can cause some problems for IT staff within a company as data can potentially be stored in many places and not be grouped with other company data.

Sometimes a company will hold small datasets within Excel worksheets rather than a database, as it can be less expensive than integrating the data.


Data forms are an often overlooked feature of Excel. They allow a user to enter data as if they are using a database form, which can cut the amount of scrolling that could be involved if using Excel for data entry. Also it makes the record that is being edited easier to read as it is not surrounded by other rows. Recent users of Excel could be excused for not knowing about this functionality. Although data forms have been included in Excel for a long time, the form function in Excel is not included in Excel toolbars by default.


Below is a screenshot of an Excel form in use:



Excel Form

Please click the button for the link to the Microsoft site on how to enable and use Excel forms for various versions of Excel:


Data entry spot check

If you are using Excel for data entry by copying and pasting data from another source, you can perform spot checks on certain fields by combining the ISNUMBER and FIND functions.


Say for example, you are entering data containing persons' names and they are entered in different fields. You could have a surname field and a field containing a full name. You would be left with two cells that would both contain a surname. Once all the data has been entered, it would be prudent to copy a formula in an extra cell as a test to see if the surname for that record is in the full name field. This could be copied down the entire column.


If cell D2 contained the surname and cell E2 contained the full name, you could use the following formula:


=IF(ISNUMBER(FIND(D2,E2)),"Surname found", "Error! - No Match")


Please note that the formula is case sensitive.

Subtracting a year

The example below shows a function that is calculating the date that is a year prior to the value in cell A1:


=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))

WEEKNUM - setting the start day of the week

WEEKNUM is a useful function that returns a week number from a date. By default, the WEEKNUM function uses Sunday as the first day of the week for calculations.


To change it to make Monday the start day of the week, use the number 2 as a return type:


=WEEKNUM(A1,2)

Extracting product category codes

If you have product codes that are made up by category code and product number, you can extract the initial category number by the use of the LEFT function.

For example, consider the following product codes:


  • TYR0001
  • TYR0005
  • WHL0099
  • WHL0432
  • TYR0732

If a code of TYR indicates a product category of 'tyre' and WHL indicates a product category of 'wheel', you can separate the three letter product code using the following function:


=LEFT(A2,3)    (where A2 is the cell containing the full product code)


It would then be possible to total up the product categories elsewhere in your worksheet.



We can also take this a step further. If the category code is not limited to three letters, but the numeric part of the code is consistently four digits. For example:


  • TYR0001
  • TYR0005
  • SCRN0099
  • WHL0432
  • SCRN0732

We can still extract the full product code by using the following formula:


=LEFT(A2,LEN(A2)-4)    (where A2 is the cell containing the full product code)



It gets a little more complicated if the length of the product category code is variable and the number is also variable, but we can still count on the product code is made up of a category code followed by a product number. For example:


  • TYR0001
  • TYR0005
  • SCRN0099
  • AIRSUS00995463
  • AIRSUS00991
  • WHL0432
  • SCRN0732

In this scenario we can count the number of letters used by using the following formula:


=SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))


If this value is stored in cell B2, we could then use the result in this formula in cell C2 to extract the product category code:


=LEFT(A2,LEN(A2)-B2)



Of course, it would be possible to combine the two formulae into one formula, but it wouldn't be fair on other people that may have to edit the formula in future. For the record, it would be:


=LEFT(A2,LEN(A2)-(SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))))


Colours - gold, silver and bronze

Gold, silver and bronze are recognised as colours of achievement, so are suitable as a cell fill colour for a top three rank. They may also represent a type of product for your organisation.
The colours gold, silver and bronze can be represented in Excel by the following custom colour codes:


Gold


     

Typed into Excel as custom colour(colour model RGB):

Red: 207

Green: 181

Blue: 59


Hex: #CFB53B

RGB: rgb(207,181,59)



Silver


     

Typed into Excel as custom colour(colour model RGB):

Red: 230

Green: 232

Blue: 250


Hex: #E6E8FA

RGB: rgb(230,232,250)



Bronze


     

Typed into Excel as custom colour(colour model RGB):

Red: 140

Green: 120

Blue: 83


Hex: #8C7853

RGB: rgb(140,120,83)

Calculating someone's age

Being able to calculate a person's age is useful in workbooks in a human resource department. The following formula will calculate someone's age in years, months and days based on a birthdate in cell A1:


=DATEDIF(A1,NOW(),"y")&" Years, "& DATEDIF(A1,NOW(),"ym")& " Months , " & DATEDIF(A1,NOW(),"md") & " Days"

Excel VBA workbook for basic UK postcode validation

This is a VBA workbook for basic validation of UK postcodes, with extra functionality for converting postcodes to uppercase.


The credit for the underlying function goes to user idbr on the Tek-tips website. The link for which is here


I used the function in a workbook to allow a user to paste in their own data to perform validation. It will automatically calculate how many rows are populated for the checks to be performed, so there is no need to manually set any ranges for the worksheet to complete the validation process.


I made an additional change to the function to improve the useability. I inserted whitespace in front of the generated messages, whereas the converted postcodes were left alone. This has the effect of changing the order of the column should it be sorted, so that the corrected postcodes will be grouped together rather than among the messages. This will make the data easier to work with if the user is tasked with manually amending the original data source based on the results of the output.


There is a small amount of test data in the workbook so that the functionality can be easily demonstrated on download, but you can safely remove this for personal use.


Simply paste your data into the 'Sheet1' worksheet in rows A to G underneath the headings. Your postcodes must reside in column G. Click the grey button on the right-hand side of your data to perform the check and create output.


Don't forget to enable macros when using the workbook

Note that this workbook is for basic validation, which ensures that the postcode patterns and codes are correct. For a full validation process, you will need the entire dataset of postcode data which contains over 1.7 million records.

Waterfall chart

Waterfall charts were introduced into Excel 2016. Click on the link to download an example of a waterfall chart compatible with earlier versions of Excel:


Waterfall Chart