Find alphanumeric values in one column and split alpha from numeric

Hi,
I'm working with a worksheet that was imported as a column of inconsistently alphanumeric values (doses). These problematic ones don't contain a space in one column and text (units) or blank in adjacent column:
E F
100 MG
20 MG
250MG
25 MG
600MG
2SROPS
2CAPSULES
250 MCG

The (F) cells with a value are okay. I want to find the combined values in E, split them so the result come out like:
E F
100 MG
20 MG
250 MG
25 MG
600 MG
2 DROPS
2 CAPSULES
250 MCG
(i.e., In the first row E already has the correct numeric value in column E - "100" and column F contains the associated UNIT - "MG". Now referring to the first problem child- E3 "250MG", the result in E3 should be "250" and F3 should be "MG".) I had been going through and manually adding a space to then apply the 'Text to columns' tool, but with over 1500 records, the next ice age would arrive before I could move on, so any VBA or function would be much appreciated, Thanks!

One way... If your range starts in E2 & F2, then in cells out to the right of your data (for instance X2 & Y2), try these formulas, fill down, then copy and use Paste Special: Values over column E & F:

X2 (Array formula, entered with Ctrl + Shift + Enter):

Formula:

Please Login or Register  to view this content.

Y2:

Formula:

Please Login or Register  to view this content.

- Moo

* h/t to C.Pearson