473,289 Members | 1,829 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

MS Access Find specific text format in string

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
Oct 18 '18 #1
4 2885
PhilOfWalton
1,430 Expert 1GB
An interesting half hour.

Not sure what you want to do with the output, but this should work (anyway it does with your example.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Function ExtractNumbers() As String
  5.  
  6.     Dim InputStr As String
  7.     Dim Lngi As Long
  8.     Dim Lngj As Long
  9.     Dim Intk As Integer
  10.     Dim SavedNumbers As String
  11.  
  12.     InputStr = "123 text more text account 1234-5678 text more text "
  13.     InputStr = InputStr & "test more text 12321324234 acct: 2345-6789 text more text "
  14.     InputStr = InputStr & "test-moretext 1234 5678 Account Number 3456-7890 text more"
  15.  
  16.     For Lngi = 1 To Len(InputStr)
  17.         If Not IsNumeric(Mid(InputStr, Lngi, 1)) And Mid(InputStr, Lngi, 1) <> "-" Then
  18.             Lngj = 1
  19.             GoTo NextLngI
  20.         End If
  21.         'Stop
  22.         If Lngj + 9 > Len(InputStr) Then        ' Past the end
  23.             Exit Function
  24.         End If
  25.  
  26.         For Lngj = 0 To 8
  27.        ' Debug.Print Mid(InputStr, Lngi + Lngj, 1)
  28.             If Lngj <= 4 Then
  29.                 If Not IsNumeric(Mid(InputStr, Lngi + Lngj, 1)) And Mid(InputStr, Lngi + Lngj, 1) <> "-" Then         ' Not a number
  30.                     GoTo NextLngI
  31.                 End If
  32.             End If
  33.             If Lngj = 4 Then                            ' Look for dash
  34.                 If Mid(InputStr, Lngi + Lngj, 1) <> "-" Then         ' Not a dash
  35.                     GoTo NextLngI
  36.                 End If
  37.             End If
  38.             If Lngj > 5 Then
  39.                 If Not IsNumeric(Mid(InputStr, Lngi + Lngj, 1)) And Mid(InputStr, Lngi + Lngj, 1) <> "-" Then          ' Not a number
  40.                     GoTo NextLngI
  41.                 End If
  42.             End If
  43.         Next Lngj
  44.  
  45.         Stop
  46.         For Intk = 0 To 8
  47.             SavedNumbers = SavedNumbers & Mid(InputStr, Lngi + Intk, 1)
  48.         Next Intk
  49.  
  50.         Debug.Print SavedNumbers
  51.         SavedNumbers = ""
  52. NextLngI:
  53.     Lngi = Lngi + Lngj - 1
  54.     Next Lngi
  55.  
  56. End Function
Note that the function should really start off with
Expand|Select|Wrap|Line Numbers
  1. Function ExtracNumbers(InputStr as String) As String
and the "InputStr" should not be mentioned on lines 6,12,13 & 14.

Phil
Oct 19 '18 #2
NeoPa
32,554 Expert Mod 16PB
Hi.

I haven't tested this but it should do the job for you.
Expand|Select|Wrap|Line Numbers
  1. Public Function ExtractVals(ByVal strInput As String) As String
  2.     Dim lngNext As Long
  3.     Dim strWork As String
  4.  
  5.     Do While strInput Like "*####-####*"
  6.         lngNext = InStr(lngNext + 1, strInput, "-")
  7.         If lngNext > 4 Then
  8.             strWork = Mid(strInput, lngNext - 4, 9)
  9.             If strWork Like "####-####" Then
  10.                 ExtractVals = ExtractVals & VbNewLine & strWork
  11.                 lngNext = lngNext + 4
  12.             End If
  13.         End If
  14.     Loop
  15.     If ExtractVals > "" Then ExtractVals = Mid(ExtractVals, 3)
  16. End Function
Oct 19 '18 #3
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)
Dec 10 '18 #4
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 ;-)
Expand|Select|Wrap|Line Numbers
  1. 'ExtractVals() extracts values that match the format ####=#### from strInput.
  2. Public Function ExtractVals(ByVal strInput As String) As String
  3.     Dim lngNext As Long
  4.     Dim strWork As String
  5.  
  6.     lngNext = 1
  7.     Do While Mid(strInput, lngNext) Like "*####-####*"
  8.         lngNext = InStr(lngNext, strInput, "-")
  9.         If lngNext > 4 Then
  10.             strWork = Mid(strInput, lngNext - 4, 9)
  11.             If strWork Like "####-####" Then
  12.                 ExtractVals = ExtractVals & vbNewLine & strWork
  13.                 lngNext = lngNext + 4
  14.             End If
  15.         End If
  16.         lngNext = lngNext + 1
  17.     Loop
  18.     If ExtractVals > "" Then ExtractVals = Mid(ExtractVals, 3)
  19. End Function
I tried the following test in the Immediate Pane with the results shown :
Expand|Select|Wrap|Line Numbers
  1. ?ExtractVals("-4444-333 try 1111-777789 if that's0000-8753-9999-");
  2. 1111-7777
  3. 0000-8753
Dec 10 '18 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

3
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"...
4
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...
7
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...
2
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.
10
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...
12
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...
1
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...
6
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...
2
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...
2
isladogs
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...
0
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"....
0
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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...
0
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...
0
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...
1
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)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.