"cosmin" wrote:

Hi all,

I am using office 2003, and I try the following: in range(a1:a:20) I have
something like "123uiun" but not always the same number of string character
or number character, they can be also "1hjjll". I want to separate string and
the number. How can I do this using VBA?

Thanks in advance

Hello,
would 2 functions

Option Explicit

Function OnlyNumber(t As String) _
As Long
Dim ArrB() As Byte, TempB As Byte
Dim L1 As Long, L2 As Long
Dim L3 As Long, TL As String

ArrB = t
L1 = UBound(ArrB)

For L2 = 0 To L1 Step LenB("A")
TempB = ArrB(L2)
If TempB 47 Then
If TempB < 58 Then
TL = TL & Chr(TempB)
End If
End If
Next L2

OnlyNumber = CLng(TL)

End Function


Function OnlyString(t As String) _
As String
Dim ArrB() As Byte, TempB As Byte
Dim L1 As Long, L2 As Long
Dim L3 As Long, TL As String

ArrB = t
L1 = UBound(ArrB)

For L2 = 0 To L1 Step LenB("A")
TempB = ArrB(L2)
If TempB < 48 Or TempB 57 Then
TL = TL & Chr(TempB)
End If
Next L2

OnlyString = TL

End Function

goodbye
r