Home 
Search 
Today's Posts 
#1




Extract digits
I have a set of strings that contain digits at the right end, such as:
s1 = "abcd1234" s2 = "g10" s3 = "yyy19123" The data will always contain alpha chars on the left, and digits on the right. What's a fast way to quickly extract the digits of each string? thanks Robert 
#2




Extract digits
Hi Robert,
Am Wed, 20 Nov 2019 10:48:20 0800 (PST) schrieb RG III: I have a set of strings that contain digits at the right end, such as: s1 = "abcd1234" s2 = "g10" s3 = "yyy19123" The data will always contain alpha chars on the left, and digits on the right. try: For i = 1 To Len(s2) If Asc(Mid(s2, i, 1)) 47 And Asc(Mid(s2, i, 1)) < 58 Then myNumber = CInt(Mid(s2, i)) Exit Sub End If Next or: Sub Test2() Dim re As Object, Match As Object Dim ptrn As String s1 = "abcd1234" s2 = "g10" s3 = "yyy19123" ptrn = "[\d]{1,}" Set re = CreateObject("vbscript.Regexp") re.pattern = ptrn re.IgnoreCase = False re.Global = True Set Match = re.Execute(s3) MsgBox Match(0).Value End Sub Regards Claus B.  Windows10 Office 2016 
#3




Extract digits
I have a set of strings that contain digits at the right end, such as:
s1 = "abcd1234" s2 = "g10" s3 = "yyy19123" The data will always contain alpha chars on the left, and digits on the right. What's a fast way to quickly extract the digits of each string? thanks Robert Try... Copy this function into a standard module in your workbook: Function FilterString$(ByVal TextIn$, Optional IncludeChars$, _ Optional IncludeLetters As Boolean = True, _ Optional IncludeNumbers As Boolean = True) ' Filters out all unwanted characters in a string. ' Arguments: TextIn The string being filtered. ' IncludeChars [Optional] Any non alphanumeric characters to keep. ' IncludeLetters [Optional] Keeps any letters. ' IncludeNumbers [Optional] Keeps any numbers. ' ' Returns: String containing only wanted characters. ' Comments: Works very fast using the Mid$() function over other methods. Const sSource As String = "FilterString()" 'The basic characters to always keep by default Const sLetters As String = "abcdefghijklmnopqrstuvwxyz" Const sNumbers As String = "0123456789" Dim i&, sKeepers$ sKeepers = IncludeChars If IncludeLetters Then _ sKeepers = sKeepers & sLetters & UCase(sLetters) If IncludeNumbers Then sKeepers = sKeepers & sNumbers For i = 1 To Len(TextIn) If InStr(sKeepers, Mid$(TextIn, i, 1)) Then _ FilterString = FilterString & Mid$(TextIn, i, 1) Next End Function 'FilterString() ...and use it like this: Values in colA Select target cells to receive filtered string Enter formula: =FilterString($A1,,FALSE) and 'viola'!  Garry Free usenet access at http://www.eternalseptember.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
how do I extract the last three digits from each cell in column?  Excel Discussion (Misc queries)  
To extract the number if there is niumeric in right most digits  Excel Discussion (Misc queries)  
how to extract digits from a row of numbers  Excel Worksheet Functions  
I need to extract the first 3 digits from a cell (ex: AAG12345)?  Excel Worksheet Functions  
Extract Digits Questions  Excel Programming 