string and numeric

[cosmin]

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

 

[Jarek Kujawa]

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

 

[Mike H]

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

 

[Jarek Kujawa]

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

 

[r]

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

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