Search Not Just Numbers

Tuesday, 4 September 2012

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".


  1. Quite confusing I must admit.
    Though I very much like the spreadsheet modelling with excel.

  2. There're even so, going going slower to guarantee many people record this market in the event chanel replica handbags gets to be major – this world’s major vogue model Louis Vuitton not long ago received a compact environmental vogue name. It truly is distinct, even so on the case connected with Nike in addition to Levis, even so that you difficulties usually are in this article to settle, for example a desire by means of European replica chanel bags of which foremost models take care of the challenge connected with technician survival into their source chain adequately. However the vogue model incorporates a somewhat limited heritage, it is author this Diego Della Valle spouse and children is usually skilled with shoe-making, having in excess of over a hundred years’ practical knowledge. The item first company is usually “J. V. Tod's”, definitely not this label or maybe nickname connected with it is author although some sort of label Diego ran into as soon as he / she seemed to be cartier replica uk the telephone directory site, in addition to he / she presumed which the concept incorporates a pleasant noise in different dialect in addition to attributes the more common The english language intonation that'll reminds persons connected with The english language aristocrat. Soon after a few years, this mail “J” in addition to “P” usually are cartier replica along with the company “Tod’s” happened. To search while using the label, it is brand was designed to possibly be a couple visualized lions, which often points out this noble significance additionally. That extravagance model is exceedingly cartier replica sale to its driving a car shoes and boots in addition to D-bag.

  3. Some of these are waterproof, dust-resistant and have advanced features like heart rate monitor, stopwatch, world clock, compass, altimeter and more. fake rolex With the advancement in technology, smart watches exist in the market. louis vuitton replica They allow you to receive and make calls. replica rolex uk A few digital watches also have an Mp3 player which allow you to listen to music. You can buy Digital watches from most retailers across the country. fake hublot But for a convenient, fun-filled shopping experience, buy them online. Online shopping stores have one catalogue with a huge collection to choose from. replica hublot Hence, you don’t have to go looking for a watch of your choice from store to store.