Search Not Just Numbers


Tuesday, 3 March 2015

Excel Tip: A quick way to add a prefix or suffix to all cells in a list

Do you ever have those situations where you need to make an amendment to the text in every cell in a range?

I know it is an issue I come across regularly. I may need to prefix all nominal ledger codes with a department reference, say, or maybe add "/1" to all existing codes in a list, so that we can add new variations later of /2, /3, etc.

We could obviously rattle down the list, hitting F2 and pasting the additional text at the start or end of each cell. However, we can be a little cleverer than that.

Say we have the second situation described above and want to add "/1" to every cell in the list. We can do this as follows.

If the list starts in cell A1, we could put the following formula in cell B1:


This earlier post explains how to use "&" to join text together, as well as some other useful methods for manipulating text.

If we copy our formula down the whole list, column B will show the new text. We can then copy column B and paste it into column A. We will, however, need to do this using Paste Special, As Values. The easiest way to do this is to copy as normal but when pasting, right-click cell A1 and click the clipboard with "123" on it.

This will paste the results of the formula in column B (as opposed to the formula itself) as text into column A (replacing the original text). We can then delete column B (which will now be showing "/1" twice in each row as it is being added to the new text in column A).

We can use the same technique with different versions of the formula to make any number of amendments to the text.

Say we want to add a department code before the code in column A and separate the two with a hyphen, we can enter the department codes in column B and enter the following formula (in column C):


Use this technique with other text manipulation functions, from the post mentioned above, to make pretty much any change you need.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 24 February 2015

Excel Tip: Number occurrences in a list

There are a number of situations, where it is useful to number each occurrence in a list.

We could place a flag in column B next to each occurrence using an IF statement, e.g.


Copying this down would place a Yes in column B next to each occurrence of North in column A, but it would not distinguish between each occurrence - they would all say Yes.

Fortunately there is a simple tip that you can use to place a number in column B, so that the first occurrence would show 1, the second 2, etc.

The principle is the same, we just need to replace "Yes" with something a little cleverer.

What we actually want now is to add 1 each time to the highest number already showing.

We can use the MAX function to find this highest number.

If we are again entering the formula in cell B2:


will return the highest number in the rows above (i.e. in cell B1).

Notice, though, the dollar signs. I have fixed both row and column on the start of the range, but only fixed the column on the end of the range. So copying this down, the end of the range would move, always going down as far as the cell above.

We can then use this formula, and add one, in place of "Yes" in our original:


Whenever North appears in column A, this will find the maximum value in column B above this cell, and add 1 to it.

So, the first time North appears, there will be nothing above the row in column B, so 0+1=1. This will therefore return a 1 in column B.

Next time North appears, the maximum value in column B above this cell, will be 1 (returned against the first occurrence), so 1+1=2, etc.

We have all of our occurrences numbered in sequence now!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 17 February 2015

Excel Tip: Identify new entries in a list

I received the following email from a reader and I thought it was a common problem that might make a good blog post.

Gill Carnell of sent me the following email regarding her work with the First Monday Business Network in North Yorkshire:

"I have been running a Free business network event for just over a year now.

I have built up a spreadsheet of approx. 150 unique names/emails of those who have attended on one or more occasions. This main list increases by half a dozen or so each month.

I paste the main list to create an invitation list to promote an upcoming meeting via eventbrite.

After a few weeks I export the event registrations to a smaller spreadsheet. It contains mainly names from my main list plus new entries who have registered for the first time.

My mission is to then send a second invitation to those on the main list who have not registered (not on small spreadsheet).

Currently I've used various combinations of VLOOKP and MATCH functions to find entries on both lists. But then I have to filter or sort and then copy resulting values to create the new mailing list.

Question is whether there is an Excel Function or you can recommend a technique that allows two spreadsheets to be compared and if duplicates occur remove both entries, so I am not inviting people who have already registered? Sort of List One subtract List Two"

I think the simplest approach would be as follows:

Add two new blank sheets to the spreadsheet that contains the Main List, so that you can paste the Registrations list into one of them each time, and use the second for the mail merge.

Add a new column to the Main List to count entries on the second list.

Say that the Main List is on a tab called MainList and  the email addresses are held in column D, whereas the Registrations are held on a tab called Registrations, and that these email addresses are in column F.

The formula in this new column (for row 2) could then be:


This can then be copied down to ensure that it covers the whole list.

This will count, how many times that email address appears on Registrations. We are only interested in the zeros (i.e. those that don't appear on the Registrations List).

Switch on Autofilter for the main list (if it is not already switched on), and filter the Count column to only show zeros, then copy the list to the Mail Merge tab, which can be already set as the source for your mail merge.

Next time you do this, just delete the contents of the two spare tabs and paste the new registrations in. Your count column and Autofilter will already be set up and you mail merge will already be set up to point at the new list when you paste it into the mail merge tab.

I hope that helps Gill, and anyone else with a similar challenge.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 10 February 2015

Excel Tip: Find the last row of data

Do you ever have a need to find the last row in a set of data - or the first gap?

I find that I use this tip quite regularly. It might be to help define a range to copy, or set a print area, or to identify where to enter the next row of data, or it might simply be that the last item in a list is highly relevant to the spreadsheet I'm developing.

Whatever the purpose, it is very handy to have a formula that will give me this information.

Say we want to know the first blank row in column A. We can use an array formula to return the row numbers of all the blank rows.

I will not go too much into array formulae here as that would be a significant blog post in itself, but there are three important things to know:

  1. An array is a set of arguments or results, arranged into rows and columns, operations can be applied to the whole array
  2. The result of an array formula will usually be an array itself and therefore will need another function to specify how it is to be shown as a single result (without this, the first result of the array will be displayed). Typical functions might be SUM or AVERAGE.
  3. To enter an array formula, you need to press Ctrl+Shift+Enter, rather than just Enter
Our array formula to return the numbers of all of the blank cells in column A would be:


when we press Ctrl+Shift+Enter it will show as:


Note that entering these curly brackets manually will not work, they must be generated by using Ctrl+Shift+Enter.

This formula will return the array containing all of the row numbers that are blank, separated by FALSE, where they are not. So, if the first 6 rows of A were:

A1 23
A2 65
A4 47
A5 12

Then the formula would return the array {FALSE,FALSE,3,FALSE,FALSE,6}

However, all we will see in the cell is the first result, i.e. FALSE. What we need is a function to return the result we do want to see, which is the row number of the first blank row. This will also, of course, be the smallest (or minimum) number in the array. Excel provides the MIN function for just this purpose, so:


will return 3, being the first blank row. Don't forget to use Ctrl+Shift+Enter to generate the curly brackets.

We can use the same logic to find the last non-blank row, which is often even more useful.
This time we want the highest (maximum) row number, where the contents are not blank, so our formula would be:


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".