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 2891
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,556
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,556
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: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |