This is a function that splits a string on numbers versus non-numbers:


Function SplitOnNumbers(strToSplit As String, _
Optional lReturnElement = -1) As Variant

Dim i As Long
Dim n As Long
Dim btArray() As Byte
Dim coll As Collection
Dim arr
Dim bNumber As Boolean
Dim bHadDecimal As Boolean

If Len(strToSplit) < 2 Then
SplitOnNumbers = strToSplit
Exit Function
End If

'make a byte array
'-----------------
btArray = strToSplit

Set coll = New Collection

For i = 0 To UBound(btArray) Step 2
If bNumber = False Then
If btArray(i) 47 And btArray(i) < 58 Then
bNumber = True
bHadDecimal = False
If i 0 Then
coll.Add Mid$(strToSplit, n / 2 + 1, (i - n) / 2)
End If
n = i
End If
Else 'If bNumber = False
If bHadDecimal Then
If btArray(i) < 48 Or btArray(i) 57 Then
bNumber = False
bHadDecimal = False
If i 0 Then
coll.Add Mid$(strToSplit, n / 2 + 1, (i - n) / 2)
End If
n = i
End If
Else 'If bHadDecimal
If btArray(i) < 44 Or btArray(i) 57 Then
bNumber = False
bHadDecimal = False
If i 0 Then
coll.Add Mid$(strToSplit, n / 2 + 1, (i - n) / 2)
End If
n = i
Else 'If btArray(i) < 44 Or btArray(i) 57
If btArray(i) = 44 Or btArray(i) = 46 Then
bHadDecimal = True
Else 'If btArray(i) = 44 Or btArray(i) = 46
If btArray(i) = 45 Or btArray(i) = 47 Then
bNumber = False
bHadDecimal = False
If i 0 Then
coll.Add Mid$(strToSplit, n / 2 + 1, (i - n) / 2)
End If
n = i
End If
End If 'If btArray(i) = 44 Or btArray(i) = 46
End If 'If btArray(i) < 44 Or btArray(i) 57
End If 'If bHadDecimal
End If 'If bNumber = False

'adding the final group
'----------------------
If i = UBound(btArray) - 1 Then
If i 0 Then
coll.Add Mid$(strToSplit, n / 2 + 1)
End If
End If
Next

'transfer collection to array
ReDim arr(1 To coll.Count)

For i = 1 To coll.Count
arr(i) = coll(i)
Next

If lReturnElement = -1 Then
SplitOnNumbers = arr
Else
SplitOnNumbers = arr(lReturnElement)
End If

End Function



Using this your problem is easy to solve, for example:

In cell A1 you have AAA1234BBBB

Put in Cell B1 the formula: =SplitOnNumbers($A$1,1)
Put in Cell B2 the formula: =SplitOnNumbers($A$1,2)
Put in Cell B3 the formula: =SplitOnNumbers($A$1,3)

You can use the same function in VBA as well, without using worksheet
functions.

Doing this with a byte array is I think (haven't tested this particular
function) faster than doing this on the string itself.


RBS


"alvin Kuiper" wrote in message
...
Hi

I have this in a cell "Text 4numbers Text "
I want to take the first text into a cell
then i want to take the 4 numbers into another cell
and then i want to take the last text into another cell

MAybe i can split by the numbers?
like take alle text before the numbers
then take the numbers and then take alle the text
after the numbers???

Hope someone can help.

Besr regards
Alvin