Excel Tip: Manipulating Account Codes

There are many times, particularly in accounting spreadsheets, where we are using codes e.g. Stock Codes, Nominal/Sales/Purchase Ledger Codes. Often there is useful information hidden within these codes, such as Bin Locations, Departments, Divisions, Regions, etc.

By pulling out this information, we can then lookup additional information, sort or filter by it, or use it for analysis in a pivot table.

But how do we get at it?

Fixed formats

The simplest situation is where the code is a fixed length and the information we need is always in the same position.

Here we can use the function LEFT, RIGHT or MID depending on its position.

=LEFT("text",x) returns the leftmost x (first) characters in "text"
=RIGHT("text",x) returns the rightmost x (last) characters in "text"
=MID("text",y,x) returns x characters starting at position y in "text"

So, where the cell A1 contains the fixed format account code 123-456-789,we can pull out any of the three sets of numbers as follows:

To return the 123, use =LEFT(A1,3)

To return the 456, use =MID(A1,5,3) and

To return the 789, use =RIGHT(A1,3)

The functions above will allow you to extract any section of a fixed format code, but what if the code has a more complex structure?

Flexible codes

Sometimes a code may have a less fixed structure, for example if in the code above, the length of the code was variable, as was the length of each of the three components - so it could take (for example) the form 12-345-67, or 1234-56-789. As long as there is some indicator of where the change is, then we can still do it. In this case we have the "-".

We can use the functions above, but we also need to use the functions FIND and LEN to calculate the arguments for those functions.

=FIND("text to find","main text"

,x) returns the position of "text to find"  in "main text" 

, starting at position x. This last argument is optional and it starts at 1 if left out.

=LEN("text") returns the length of "text", in characters.

so where A1 again contains the account code 123-456-789

=LEFT(A1,FIND("-",A1)-1) returns 123

Explanation:  FIND("-",A1) returns the position of the first "-" and by deducting 1 we have calculated the position of the last character of the first section (3) and therefore the x argument for the LEFT function

=MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-1-FIND("-",A1)) returns 456

Explanation: Not as scary as it looks when you break down the three arguments for the MID function.

The first argument is the easy one, just the cell with the code in, A1

The second argument, being the starting point of the code we are looking for is FIND("-",A1)+1, which is simply the position of the first "-", plus 1.

The third argument is the tricky one which needs to find the number of characters to return. We do this by finding the position of the character before the second "-" and deducting the position of the first "-"

FIND("-",A1,FIND("-",A1)+1) returns the position of the second "-" by using the FIND function again but using the optional start position argument so that we do not start looking until the character after the first "-". We have already worked out a formula to find this position (as it was the second argument of the MID function. We then deduct 1 to get the character before it.

FIND("-",A1) returns the position of the first "-" which we deduct, giving the full third argument of the MID function as FIND("-",A1,FIND("-",A1)+1)-1-FIND("-",A1).

=RIGHT(A1,LEN(A1)-FIND("-",A1,FIND("-",A1)+1)) returns 789

Explanation: Here we use the length of the string minus the position of the second "-" (calculated as above) to determine the number of characters to return from the RIGHT function.

There are many other possibilities, but a combination of these functions should give you a route to pull out pretty much anything from a code.

Good Luck!

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

EXCEL TIP: Identifying irregular period-ends

A couple of weeks ago I did a post on identifying the calendar month-end from a particular date, and I promised to explain how to do this when your month-ends are irregular - as is still quite common in a manufacturing environment. Here is that post.

You may have any set of rules that govern when the month-end (or period-end if they're really irregular) is - "last Friday of the month" for example, but there are nearly always exceptions - usually around Christmas and other holidays.

The most flexible way to address this issue is to have a table of month-ends held in the spreadsheet that can be adjusted to whatever you want.

This can be a simple two column table with period start-dates in the first column and period end-dates in the second.

You can then use VLOOKUP to find the first start date that is before the transaction date in question. We can then return the period-end date from the second column.

For example, where the range containing the two-column table described above is called PERIODENDS and the transaction date in question is held in cell A1, we can use:

=VLOOKUP(A1,PERIODENDS,2) to return the corresponding period-end date.

If we wished to return the period number, we could add this in a third column in the table and use:

=VLOOKUP(A1,PERIODENDS,3)

Notice that I have not entered the optional fourth argument in the VLOOKUP function [range lookup]. This argument is normally entered as FALSE if you wish to look for an exact match, but if omitted (or entered as TRUE) the VLOOKUP identifies the first entry in the column (the column must be in ascending order) which is exceeded by the lookup value. In this case, the first period start date that the transaction date exceeds - i.e. exactly what we want!


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

EXCEL TIP: Making Accounting periods useful in Excel

Often we have data, particularly in accounting systems, stored by Year and Period Number.

This may be weekly takings, with year being the accounting year and the period number being the week number, or it might simply be account movements or payroll by accounting year and accounting period, 1 to 12.

This is a sensible way to store the data but it can make selecting a range quite tricky, so I thought I would share a little trick I use to simplify this problem.

The problem:


Where data has a date attached, for example a transaction date, or a month end date for the type of data we are discussing here, we can use the following IF statement to determine whether a transaction/movement is within a date range:

Where A2 holds the transaction date and G1 holds the Start Date of the range we want to look at and H1 holds the End Date.

=IF(AND(A2>=$G$1,A2<=$H$1),"Yes","No")

Answers the question whether A2 is within the range G1 to H1.

(If you need a refresher on the IF statement, take a look at my earlier post).

Where we have a Year and Period Number, this doesn't work. If the range straddles a year end, you want to include, say, period 12 from the first year but not from the second.

The Solution:

To make it much easier, we need to create a working column that combines the two into a sequential number. We can do this with a simple calculation:

Where B2 holds the year and C2 holds the period we can enter the following in D2:

=(B2*100)+C2

This will convert the year and period into a 6-digit number where all periods are in sequence, so...

Where B2=2012 and C2 =7, the formula returns 201200 + 7 = 201207

It is possible that the year and period are held as text rather than as a number, in this case you would use the VALUE function to convert the text to values:

=(VALUE(B2)*100)+VALUE(C2)

If we now state the start and end of the period range in the same format (in cells G1 and H1 as before), we can use the same approach as for dates to identify the range using our working column D, i.e.

=IF(AND(D2>=$G$1,D2<=$H$1),"Yes","No")

Column D could also be used to sort the data, as we now how have a column that identifies the correct chronological order.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

EXCEL TIP: Finding the month end

Here is a useful tip for finding the month-end from a transaction date

I often have a need to identify the month-end date relating to a particular transaction during the month, i.e. we have the date of the transaction and need to return the date of the month-end.

NB: Most businesses in my experience tend to use calendar months for accounting periods these days, however some (particularly in manufacturing) still have month-ends on a particular day of the week. My tip below will only work for calendar months, but I may cover irregular month-ends in a future post (hint, you will need VLOOKUP).


Another NB: It has been pointed out to me by a number of readers (see James Travers comment below) that there is a quicker solution than that outlined below for users of Excel 2010. It uses a function which I didn't know was there as I didn't need it, having an approach that served me well in earlier versions. In Excel 2010, you can use =EOMONTH(A1,0), where A1 contains the transaction date. The method below is still worth knowing as you will need it if a file has to be accessed by earlier Excel versions. It also uses a number of techniques for manipulating dates that you can use for other purposes.

Here we can use an interesting and very useful feature of the DATE function, along with the YEAR and MONTH functions.

The DATE function returns a date given the year month and day, and is entered as follows:

=DATE(year,month,day)

so =DATE(2012,7,16) returns 16th July 2012 (in whatever date format you have set for the cell).

The YEAR, MONTH and DAY functions work the other way, in that they pull out the year, month and day numbers respectively from a date, so if cell A1 contains the date 16th July 2012, =YEAR(A1) will return 2012.

We could use this to pull out a particular day of the month from any transaction, so if we wanted to return the 25th day of the month of the transaction (where the transaction date,16th July 2012, is held in cell A1), we could use:

=DATE(YEAR(A1),MONTH(A1),25)

which would return 25th July 2012.

However, the actual month-end day obviously varies between the 28th and the 31st. This is where the useful quirk of the DATE formula comes in. Day zero of the month is the last day of the previous month, so

DATE(2012,8,0) returns 31st July 2012.

We can therefore amend the DATE formula above to provide the month end for the transaction date in A1,  to provide:

The Answer:

=DATE(YEAR(A1),MONTH(A1)+1,0)

And don't worry, those of you who are seeing a potential problem around the year end, when MONTH(A1) equals 13. The month formula works in a similarly useful way, so that month 13 of 2012 is month 1 of 2013, so:

=DATE(2012,13,0) returns 31st December 2012 or even;

=DATE(2012,15,25) returns 25th March 2013.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".