Thread: Find and extract string of 16 digits (2002)

1. Find and extract string of 16 digits (2002)

I have inherited the code below which extracts a 16 digit numeric string from a variable string where the 16 digits can be anywhere within. It works, however, sometimes the 16 digit string may contain one, two or even three spaces, at which point, the extract fails. Would there be a way to 'fix' this? Maybe a second loop to extract the first 16 numeric digits found, if the first loop failed?

Option Explicit

Public Sub GetNumber()

Dim sParts
Dim sPart
Dim c As Range
Dim cRow As Long
Dim sVal As String

'Set the late binding objects
Dim rPart As Object

Set rPart = CreateObject("VBScript.RegExp")
rPart.Global = True
rPart.IgnoreCase = True

For Each c In Sheets("GET NUMBER").UsedRange.Columns(4).Cells

sVal = c.value
cRow = c.Row

'Set matches, using regular expressions to contain the numeric chars
rPart.Pattern = "d{16}"
'get the results
Set sParts = rPart.Execute(sVal)
'get the first match
For Each sPart In sParts
'the character part is equal to the total string - the lenght of the numeric part
c.Offset(0, 5).value = sPart
Exit For
Next
Set sParts = Nothing

Next

Set sParts = Nothing
Set sPart = Nothing
Set rPart = Nothing

End Sub

2. Re: Find and extract string of 16 digits (2002)

If there's spaces in between, it's not 16 digits, innit? Please explain more clearly what you want.

3. Re: Find and extract string of 16 digits (2002)

See attached:
The red cells are not populated due to spaces. Could another loop extract the first available 16 numerics that are found, ignoring spaces??

Thanks

4. Re: Find and extract string of 16 digits (2002)

Change the line
<code>
sVal = c.Value
</code>
to
<code>
sVal = Replace(c.Value, " ", "")</code>

5. Re: Find and extract string of 16 digits (2002)

<img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> I'm lost for words......

6. Re: Find and extract string of 16 digits (2002)

Firstly, I know the OP is looking for VBA solution and HansV already solved the problem

However, in count of the VBA solution, I find that using 612 characters

The following Formula solution , just posted for your information only,

The 2 Formula solution, counted in use 70 and 60 characters respectively, only around 10% of the VBA solution

1] =MID(TRIM(D9),MIN(FIND({0,1,2,3,4,5,6,7,8,9},TRIM( D9)&1234567890)),16)

2] {=MID(TRIM(D9),MIN((FIND(ROW(\$1:\$10)-1,TRIM(D9)&5^19))),16)}

Regards
Bosco

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•