Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
string and numeric
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
string and numeric
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
string and numeric
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
string and numeric
to use the macro first you need to select the range(a1:a:20)
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
string and numeric
"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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
string and numeric
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
string and numeric
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text string to Numeric string | Excel Discussion (Misc queries) | |||
Find Numeric sign in a string | Excel Worksheet Functions | |||
How do I replace last numeric string from a alphanumeric string? | Excel Discussion (Misc queries) | |||
doesn't recognize fraction in string as numeric | Excel Programming | |||
Find numeric value at end of string | Excel Worksheet Functions |