Dividing a letters and numbers string

[konkursor]

Hello,

Recently I've stumbled upon a small problem, which I just cannot overcome.

A given string of some letters (from three to five, six) is followed by a single number (1-4). Then it ends, or is followed by another such combination, up to a maximum of, let's say, four.

Example:
xxxx1
yyy3yyyy1
xyzxx1yxz4xzx1
xxx2xxx2xxx2xxx2

What I would like to do is to divide a given chain of characters into the smallest chains which contain only letters and one number. To the examples given above, I'd like to receive the following sets:
xxxx1
yyy3 and yyyy1
xyzxx1 and yxz4 and xzx1
... and so on

Is it somehow possible to do it with simple functions? Or is VB necessary (which sadly I don't know)?

Thanks,
Konkursor

[mrice]

Here's some VBA that will do it.

Please Login or Register  to view this content.

[shg]

Here's a UDF you could use:

Please Login or Register  to view this content.

... and an example of its usage:

Please Login or Register  to view this content.

To add it to the workbook:
1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Close the VBE to return to Excel

It requires an array formula. So for the example above, select cells A1:F1, and enter the formula in the Formula Bar without the curly braces. Then hold down the Ctrl and Shift keys, then press Enter. The curly braces will appear. Then you can drag down the fill handle to copy the formula down.

[konkursor]

Both of your formulas are working great, thank you!

Just one more question, as apparently it turned out that there are some exceptions in the number strings. Sometimes, in the last part of the chain (or first being at the same time the last, if there are no numbers whatsoever) there are no numbers.
For example with xyz4zyx is it possible to make it print out xyz4 and zyx1 (zyx from the end plus 1 added by the code). Maybe by somehow ordering it to print out everything what is left with an additional 1 after all the other cells have been done?

Really sorry for the trouble, as I didn't notice it before...

[shg]

Please Login or Register  to view this content.

[mrice]

Here's a VBA version which copes with no numbers at the end.

Please Login or Register  to view this content.

[konkursor]

Thank you very much, I really appreciate it!