Hi. Thank you in advance for helping or attempting to help.
I have an access DB with a memo field. This memo field contains lots of information, which is hand-typed and not consistent.
I need to pull from this field a string the matches the following format
####-####
(That being 4 numbers, a dash, then 4 numbers.)
I would prefer to use a built in function, but am comfortable with using VBA.
Examples:
123 text more text account 1234-5678 text more text
test more text 12321324234 acct: 2345-6789 text more text
test-moretext 1234 5678 Account Number 3456-7890 text more
Results would return:
1234-5678
2345-6789
3456-7890
4 2885
An interesting half hour.
Not sure what you want to do with the output, but this should work (anyway it does with your example. -
Option Compare Database
-
Option Explicit
-
-
Function ExtractNumbers() As String
-
-
Dim InputStr As String
-
Dim Lngi As Long
-
Dim Lngj As Long
-
Dim Intk As Integer
-
Dim SavedNumbers As String
-
-
InputStr = "123 text more text account 1234-5678 text more text "
-
InputStr = InputStr & "test more text 12321324234 acct: 2345-6789 text more text "
-
InputStr = InputStr & "test-moretext 1234 5678 Account Number 3456-7890 text more"
-
-
For Lngi = 1 To Len(InputStr)
-
If Not IsNumeric(Mid(InputStr, Lngi, 1)) And Mid(InputStr, Lngi, 1) <> "-" Then
-
Lngj = 1
-
GoTo NextLngI
-
End If
-
'Stop
-
If Lngj + 9 > Len(InputStr) Then ' Past the end
-
Exit Function
-
End If
-
-
For Lngj = 0 To 8
-
' Debug.Print Mid(InputStr, Lngi + Lngj, 1)
-
If Lngj <= 4 Then
-
If Not IsNumeric(Mid(InputStr, Lngi + Lngj, 1)) And Mid(InputStr, Lngi + Lngj, 1) <> "-" Then ' Not a number
-
GoTo NextLngI
-
End If
-
End If
-
If Lngj = 4 Then ' Look for dash
-
If Mid(InputStr, Lngi + Lngj, 1) <> "-" Then ' Not a dash
-
GoTo NextLngI
-
End If
-
End If
-
If Lngj > 5 Then
-
If Not IsNumeric(Mid(InputStr, Lngi + Lngj, 1)) And Mid(InputStr, Lngi + Lngj, 1) <> "-" Then ' Not a number
-
GoTo NextLngI
-
End If
-
End If
-
Next Lngj
-
-
Stop
-
For Intk = 0 To 8
-
SavedNumbers = SavedNumbers & Mid(InputStr, Lngi + Intk, 1)
-
Next Intk
-
-
Debug.Print SavedNumbers
-
SavedNumbers = ""
-
NextLngI:
-
Lngi = Lngi + Lngj - 1
-
Next Lngi
-
-
End Function
Note that the function should really start off with - Function ExtracNumbers(InputStr as String) As String
and the "InputStr" should not be mentioned on lines 6,12,13 & 14.
Phil
NeoPa 32,554
Expert Mod 16PB
Hi.
I haven't tested this but it should do the job for you. - Public Function ExtractVals(ByVal strInput As String) As String
-
Dim lngNext As Long
-
Dim strWork As String
-
-
Do While strInput Like "*####-####*"
-
lngNext = InStr(lngNext + 1, strInput, "-")
-
If lngNext > 4 Then
-
strWork = Mid(strInput, lngNext - 4, 9)
-
If strWork Like "####-####" Then
-
ExtractVals = ExtractVals & VbNewLine & strWork
-
lngNext = lngNext + 4
-
End If
-
End If
-
Loop
-
If ExtractVals > "" Then ExtractVals = Mid(ExtractVals, 3)
-
End Function
I appreciate your help with this. I tried to put this into use, but it seems to hang up every time I attempt to use it. (Even on a sample record set containing only 8 records)
NeoPa 32,554
Expert Mod 16PB
When I tested it, it came up with similar results :-( It was busy in an interminable loop.
Try this revised version. I've tested it this time ;-) - 'ExtractVals() extracts values that match the format ####=#### from strInput.
-
Public Function ExtractVals(ByVal strInput As String) As String
-
Dim lngNext As Long
-
Dim strWork As String
-
-
lngNext = 1
-
Do While Mid(strInput, lngNext) Like "*####-####*"
-
lngNext = InStr(lngNext, strInput, "-")
-
If lngNext > 4 Then
-
strWork = Mid(strInput, lngNext - 4, 9)
-
If strWork Like "####-####" Then
-
ExtractVals = ExtractVals & vbNewLine & strWork
-
lngNext = lngNext + 4
-
End If
-
End If
-
lngNext = lngNext + 1
-
Loop
-
If ExtractVals > "" Then ExtractVals = Mid(ExtractVals, 3)
-
End Function
I tried the following test in the Immediate Pane with the results shown : - ?ExtractVals("-4444-333 try 1111-777789 if that's0000-8753-9999-");
-
1111-7777
-
0000-8753
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Robert Oschler |
last post by:
What's a good way to find a specific text node element in a web page's DOM
tree? I thought of traversing each node but there has to be a faster way.
Is there a "find text node by nodeValue"...
|
by: searider86 |
last post by:
Does anyone know if there is a snippet of code that automatically
removes the wrap text format in excel?
I have an access form that displays data and allows the user to click
on a cmdbutton to...
|
by: Eddy Soeparmin |
last post by:
Hi,
I need to display a DateTime field in 'mm/dd/yyyy' in a DataGrid.. On
myGrid1 - Properties - Columns - myColumn1 - Text format string:
I tried to put 'mm/dd/yyyy' in there and it displays...
|
by: Mike Kansky |
last post by:
Is there a built in function in .NET to find URL in a string so i can format
it as <a href="url" ?
useful for Forum Posts and other human entered text.
|
by: tom |
last post by:
Im trying understand format string vulnerability. Source along
Erickson's HACKING: The Art of Exploitation.
#include <stdlib.h>
int main(int argc, char *argv){
char text;
static int...
|
by: param |
last post by:
Hi All,
I want to use %x as directive to get the locale specific date
representation. But, at the same time, i want to know the format of
the output for interpreting it through program. Is there...
|
by: Arielle |
last post by:
The Problem: Currently working on a way to catalog a large flux of files into a database. The files are for the most part uniform but some of the flags in the database fields vary depending on the...
|
by: and1 |
last post by:
hello .. hmm currently i'm working on vba in excel, apparently i use ADO to extract a table data from access to excel and it works fine. the problem is when i use the extracted data to create a chart...
|
by: edkreuz |
last post by:
I have moved a database from work to home and it went from 2010 to 2013. Most of my fields are functioning correctly in my forms, but I have one field that is a combo dropdown that will not display...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM).
In this month's session, the creator of the excellent VBE...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| |