Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 20th 19, 07:48 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 64
Default 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   Report Post  
Old November 20th 19, 08:13 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,731
Default 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   Report Post  
Old November 21st 19, 12:25 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,083
Default 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 alpha-numeric 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.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I extract the last three digits from each cell in column? PeggyT Excel Discussion (Misc queries) 4 November 28th 09 08:01 PM
To extract the number if there is niumeric in right most digits pol Excel Discussion (Misc queries) 4 October 16th 08 07:21 AM
how to extract digits from a row of numbers preydd Excel Worksheet Functions 2 September 3rd 08 04:32 PM
I need to extract the first 3 digits from a cell (ex: AAG12345)? Rob Excel Worksheet Functions 6 April 21st 07 11:45 PM
Extract Digits Questions [email protected] Excel Programming 5 December 27th 05 11:34 PM


All times are GMT +1. The time now is 02:45 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017