Looks like a job for String Functions

Why not have some fun with some RegEx?

HI Mark, you need to watch the video Richard just mentioned, because you need some of those string functions, and some creativity as well.

First is the creative part: you need to replace all occurrences of "(" with "|(", and all occurrences of ")" with ")|".  Your string will become this, with each number delimited by a "|":

     1 |(1)| 2 |(2)| 3 |(3)| 4 |(4)|

Second, use the Split() function to put all the numbers in an array, using "|" as the delimiter.  Your array will have 9 elements, the first being "1 ", second "(1)", third " 2 ", fourth "(2)", and so on.  There are leading and trailing spaces in some elements, as shown.

Then you use a For Next loop to go through each element in the array.  Use the Trim() function to remove the leading and trailing spaces in each of them.  Then check the presence of "(" in each element.  If an element begins with "(", put the number in one field.  If it doesn't, the other field.

Regarding the numbers with the parentheses, you didn't say whether you want to store the parentheses with the numbers into the field or not.  If not, you need to remove the parentheses in those numbers with the Replace() function as well.

Alex, I believe this is what he needs:

^((\s*[\d]+\s*)(\(\s*\d+\s*\)))+

Good idea with the |

But it will cause problems if "|" is contained inside the strings.  In my old job, I used really obscure characters as delimiters, which was still not 100% perfect.  The best way is to avoid using delimiters.  So I actually second the use of regular expressions as others have suggested.  Richard, you may want to add a course on that.  It's not exactly a database topic, so it's widely used in any coding languages, and it's supported in VBA (see picture below).

Yes. Alt-code character charts can be found all over the INTERNET. I often use "https://www.alt-codes.net/" and things like "►", chr(16) or "'", chr(187) as well as many others can be useful.