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

would this macro help?

Sub cus()
Dim tekst as Integer

For Each cell In Selection

For i = 1 To Len(cell)
If IsNumeric(Mid(cell, i, 1)) Then
tekst = Len(cell) - i
Else:
Exit For
End If
Next i

cell.Offset(0, 1) = Left(cell, i - 1)
cell.Offset(0, 2) = Mid(cell, i, tekst)

Next cell

End Sub

Hi,

Right click your sheet tab, view code and paste this in

Sub extractnumbers()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim myrange As Range, C As Range, Outstring As String
Set RegExp = CreateObject("vbscript.RegExp")
RegExp.Global = True
For x = 1 To 2
If x = 1 Then
RegExp.Pattern = "\d"
Else
RegExp.Pattern = "\D"
End If
Set myrange = ActiveSheet.Range("a1:a20") '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
Next
End Sub

Mike

"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

to use the macro first you need to select the range(a1:a:20)

"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

On Fri, 1 Aug 2008 01:19:01 -0700, Mike H
wrote:

Hi,

Right click your sheet tab, view code and paste this in

Sub extractnumbers()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim myrange As Range, C As Range, Outstring As String
Set RegExp = CreateObject("vbscript.RegExp")
RegExp.Global = True
For x = 1 To 2
If x = 1 Then
RegExp.Pattern = "\d"
Else
RegExp.Pattern = "\D"
End If
Set myrange = ActiveSheet.Range("a1:a20") '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
Next
End Sub

Mike

If you are going to use regular expressions, I believe this is simpler:

==================
Sub SeparateNumbersAndLetters()
Dim c As Range
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True

For Each c In Selection 'change to suit
re.Pattern = "\d"
c.Offset(0, 1).Value = re.Replace(c.Value, "")
re.Pattern = "\D"
c.Offset(0, 2).Value = re.Replace(c.Value, "")
Next c

End Sub
=========================
--ron

Is there any special reason you want to do this in VBA? It would be more
efficient to use worksheet formulas...

B1: =LOOKUP(9E+99,--LEFT(A1,ROW($1:$99)))
C1: =SUBSTITUTE(A1,B1,"")

Copy them down to row 20.

Rick


"cosmin" wrote in message
...
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