Valerie,

Whether this is elegant or not I'll let you decide but one way is to right
click the sheet tab, view code, paste this in and run it. None numbers will
extract to column B and numbers to column C.

Sub extractnumbers()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim Myrange As Range, C As Range, Outstring As String
For x = 1 To 2
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
If x = 1 Then
.Pattern = "\D"
Else
.Pattern = "\d"
End If
End With
Set Myrange = ActiveSheet.Range("a1:a100") 'change to suit
For Each C In Myrange
Outstring = ""
Set Collection = RegExp.Execute(C.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
C.Offset(0, x) = Outstring
Next
Set Collection = Nothing
Set RegExp = Nothing
Set Myrange = Nothing
Next
End Sub

Mike

"Valeria" wrote:

Dear experts,
I have a worksheet containing many cells of text + numbers + spaces (the
first cell of each row), like for cell A1:
text 12 text 34 text 67 1234567 abc % ef 12345678R019827 2

where "abc", "%" and "ef" remain always the same, all others very in length
and the first set varies in text+numbers combinations (there may or may not
be numbers, and when there are, their position is aleatory). There is always
one fixed space between the "abc" and the numbers preceeding. There are
always 5 spaces between the "ef" and the numbers after that. Between the
first set of text+numbers and the numbers after (the "1234567") there is
always at minimum 2 spaces.

I would like to be able to separate each set in a different cell. To be clear:
A2: text 12 text 34 text 67
A3: 1234567
a4: abc
etc

Could you please let me know what is the most efficient and elegant way to
do this in VBA?
Many thanks.
Best regards

Valeria