Parse numeric substring, generate accounting key


New Member
Jun 30, 2020
I'm a full time software developer, helping out a charity with their Access (.accdb) database in my spare time. There I need to work with an existing database that connects basic entities from one table with actions to perform on them in another table; everything is properly linked (1 entity -> n actions) and works as expected.
Now I have to add a code following a specific structure extracted from existing columns to the actions to make bookkeeping happy, and I encountered some basically simple issues I have problems solving with Access. I do not own a copy of Access (or Windows, for that matter), so I can't play around too much exploring the Access "API". Any help is appreciated, especially if it uses as little VBA as possible (where do I put that in the first place? in addition, commands are localised so VBA-solutions are of limited use on my regional system anyways); I'm fine with extensive SQL, though.

The rows used to calculate the key will contain, among others:

  internal_region_id: Integer,
  human_readable_entity_id: String.matching(/\w{2}\d+/),
  date: Date
The key will be a string value, constructed as follows: `<constant prefix><string from internal_region_id><calculated key string from human_readable_entity_id><last two digits of year from date>`.
  1. Will simply using the concat symbol & be enough to concatenate a string with a numeric value or will I need to to string conversion first?
  2. The calculated key value will be a starting number plus a number that must be parsed from the substring of another column's value. The column in question has a two-letter prefix followed by the numeric part without separators. Basically I need to calculate something like `100 + getTheNumericPartOf("ab123")` where the number of digits may vary. Reading through the documentation of string functions I could not find anything that would remove n chars from the beginning of a string or match a regex, so what would be the simplest way of getting that numeric part?
  3. Getting the last two digits of the year (date value) should be easy by reading up a little on the string format functions, right?

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.


MrExcel MVP, Moderator
Mar 2, 2007
Office Version
Hi, in MSAccess the MID function would get the last digits of a string skipping the first two:
SELECT Mid(MyField,3) FROM MyTable
Last two digits of the year could in fact be the same (Right() would also work):
SELECT mid(year(Date()),3) FROM MyTable

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...