How to extract a generic string (pattern) of character & digits in VBA?

I'm trying to build a function on which I'm blocked since a long time now.

I would like, from a string in a cell, extract a sub-string like "ABC123" or "AB1234". There are some conditions :

• It must be like "ABC123" or "AB1234" so 3 char and 3 Numeric or 2 char and 4 Numeric. I.e. "CDE789" is therefore also one of the pattern.
• The String in which we are looking has no particular size. It may contain, or not, the pattern.
• If the search term should have a space after or before (then only that pattern). Not that it doesn't apply if we are at the beginning of the string or at the end.
• The function should return that particular set of string (i.e. in a column besides that already exist).

I tried the like function, arrays or even dictionary but I can't find a solution. For instance I tried to loop trough the LEN of the string, subdivide in 6 variables and check with an IF if the first 2 where char and the last 4 where digits. It's quite heavy and not applicable for a large set of data.

Also please apologies for my broken English, do not hesitate to correct me, I'm also her to learn this :)

Thank you !