I would really encourage you to all read this post very carefully and look at the demo. I explain a fundamental technique that I hope will change the way you think about problems like this.
I am not sure if this something I can do in a query using LEN along with LEFT/MID/RIGHT/INSTR or if it would require a function be created in VBA.
Generally most developers confronted by this actually quite complex problem would make a custom function as have the other posters. The problem with these techniques is that all the data is fed through what is effectively a tiny hole, a single character at a time.
Arnelg's Regular Expression is no exception, it just has the tiny hole hidden below the surface in the Regex. We discovered this a while back when Isladogs compared RegEx against custom VBA functions after I suggested RegEx in a solution. RegEx is actually slower, probably because it is so powerful and is able to recognise a vast variety of patterns. rather than be focused on the simple pattern required.
These techniques are what is known by database professionals as RBAR (pronounced "re-bar") which means "Row By Agonising Row". It isn't efficient because it doesn't make use of the database engine's ability to process data as sets and it has to call the function which is interpreted code not like the native functions in SQL. Consequently, while the function works well on small numbers of records it is relatively slow on a large dataset.
The database I have attached shows how to do this task using a query that only employs database engine capabilities to get the results. It is not an especially pretty example of the technique. I just threw together something that worked in this situation. I expect it could be nicer if I gave it some more thought (or used TSQL). I would also normally write this as embedded subqueries but it is much easier to see how it works in stages.
The crux is the query
GetChar which uses what is called a
Tally table. A Tally table is just a set of records with integers. (There are several ways to manifest them without storing a table but this one is just a simple table.) Using a Cartesian Product (ie no join) to the data, the Tally table is used to drive
Mid() , returning a record for every character in the data along with its recordID and its position in the record.
Basically this process completely shreds the data and tags it. This is not an intuitive thing for a human to do trying to solve this problem but it is the way database engines function best. Lots of records, few columns and simple relationships.
The rest of the queries just manipulate the data into the form we need. It is complicated a little by the variable number of groups.
GetAlpha and
GetNum separate the Alpha and Numeric records from
GetChar using
Like "[0-9]" for Numerals and
Like "[a-z]" for characters.
GetLastAlpha and
GetLastNum find the last alpha and numeric characters in the groups by checking if the subsequent character in a record is the opposite type. It can only be viewed in SQL because of the join type is not supported in the designer. They then Union the last Alpha and Numeric characters in the string (respectively) because they are not followed by an opposite type.
GetLastAlpha also Unions a Null for each record. Without it, none of the two group results are returned in the subsequent query. It is much simpler than dealing with what I expect would be some really awful logic.
Results puts the combinations of character positions back into single records. The two group and three group variations of those with three groups are both returned. A more general solution with an arbitrary number of groups might use a Crosstab but it wouldn't be straightforward.
Final rejects the two group results for those with three groups, calculates the Answer and joins back to the data so it can be displayed with the results.
I have not clocked it against the VBA functions but I have little doubt it would hugely outperform the functions on large numbers of records. Anyone care to add a heap of records and import the functions to see? I need to go to bed.