Extract text from a field in access (1 Viewer)

xmattxman

New member
Local time
Today, 09:15
Joined
Jun 3, 2021
Messages
5
AH0370400091
27​
CUT (09GN20T-01) 73" LONG
AH0370400092
25​
(09GN20T-01)CUT 6.50" - STRIP
AH0370400093
20​
CUT 2 09GN20T-01 LEADS
AH0370400093
80​
(09BN22R-01) CUT 3.5"

I need to extract only the numbers that start with 09 from the text in the far right field. I tried left trim but cant seem to get it to work properly. Can someone point me in the correct direction to extract the 09 numbers for this field?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:15
Joined
Jul 9, 2003
Messages
13,357
I am confused. Why show three fields when you only need to extract the number from one field?
It would make more sense just to show the single field.
is there something you're not telling us?
 

plog

Banishment Pending
Local time
Today, 08:15
Joined
May 11, 2011
Messages
10,562
In all those instances the only numbers that start with "09" is 9. Perhaps your definition of "numbers" is different than everyone else's though.

You can use InStr (https://www.techonthenet.com/access/functions/string/instr.php) to find the position of where a substring starts in a larger string. With that you can always find the first occurence of "09" in your field. Then you need to define characteristics of your number--specifically where it ends.

If it's a set number of characters after the "09" you can simply use Mid(https://www.techonthenet.com/access/functions/string/mid.php). If its variable in length and delimited by another character--a hyphen, a space, etc.--then you will have to build a custom function to find that and then extract it.

It might be best to demonstrate what you hope to extract out of all the examples you gave.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:15
Joined
Oct 29, 2018
Messages
16,078
Hi. Welcome to AWF!

If there's a specific pattern to the data you want to extract, you might be able to use Regular Expressions.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:15
Joined
Feb 28, 2001
Messages
20,625
Help us to help you. Take your 4-line example and give us a list of what you would WANT to see returned in each case.
 

xmattxman

New member
Local time
Today, 09:15
Joined
Jun 3, 2021
Messages
5
Help us to help you. Take your 4-line example and give us a list of what you would WANT to see returned in each case.
original - CUT (09GN20T-01) 73" LONG result 09GN20T-01
original - (09GN20T-01)CUT 6.50" - STRIP result 09GN20T-01
original - CUT 2 09GN20T-01 LEADS result 09GN20T-01
original - (09BN22R-01) CUT 3.5" result 09BN22R-01

Everything I am extracting begins with 09

sorry about calling them numbers we call them part #s here.
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:15
Joined
Sep 21, 2011
Messages
8,752
So always same length of 10 chars?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:15
Joined
Oct 29, 2018
Messages
16,078
original - CUT (09GN20T-01) 73" LONG result 09GN20T-01
original - (09GN20T-01)CUT 6.50" - STRIP result 09GN20T-01
original - CUT 2 09GN20T-01 LEADS result 09GN20T-01
original - (09BN22R-01) CUT 3.5" result 09BN22R-01

Everything I am extracting begins with 09

sorry about calling them numbers we call them part #s here.
Seems like a possible RegEx pattern could be "09\w+-\d{1,2}"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:15
Joined
Oct 29, 2018
Messages
16,078
No sometimes one could have more characters . Could we do something like anything that starts with 09 up to the first space?
Hi. Are you saying the "-01" part could actually have more than two digits? Can they also have alpha chars in them?
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:15
Joined
Sep 21, 2011
Messages
8,752
No sometimes one could have more characters . Could we do something like anything that starts with 09 up to the first space?
Yes, use the Instr() suggested,once for 09 and then for space, do the math and use Mid().
I would do it that way as I do not know regex that well.
 

xmattxman

New member
Local time
Today, 09:15
Joined
Jun 3, 2021
Messages
5
On further review it looks like they are all 10 characters long and will always end with two numbers at the end
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:15
Joined
Oct 29, 2018
Messages
16,078
On further review it looks like they are all 10 characters long and will always end with two numbers at the end
Okay, if you decide to try Regular Expressions, you could try the pattern I posted earlier.
 

Mike Krailo

Active member
Local time
Today, 09:15
Joined
Mar 28, 2020
Messages
310
Assuming 10 characters and the table name is PN, original part number in field PNum:

Code:
SELECT PN.PNum, Left([PNum],10) AS NewPartNo
FROM PN
WHERE (((PN.PNum) Like "90*"));
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:15
Joined
May 7, 2009
Messages
13,691
using dbguys Pattern ("09\w+-\d{1,2}"):
Code:
Public Function fnRegExpReplace( _
                    ByVal pSourceText As String, _
                    ByVal pPattern As String, _
                    Optional ByVal pReplacement As String = "")
    Dim varValue As Variant
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = pPattern
        varValue = .Execute(pSourceText)(0)
        
    End With
    fnRegExpReplace = varValue
End Function

Private Sub test()
Dim arrString(1 To 4) As String
Dim i As Integer
arrString(1) = " CUT (09GN20T-01) 73"" LONG"
arrString(2) = "(09GN20T-01)CUT 6.50"" - STRIP"
arrString(3) = "CUT 2 09GN20T-01 LEADS"
arrString(4) = "(09BN22R-01) CUT 3.5"""

For i = 1 To 4
    Debug.Print fnRegExpReplace(arrString(i), "09\w+-\d{1,2}")
Next

End Sub

result of Test() sub:

Code:
09GN20T-01
09GN20T-01
09GN20T-01
09BN22R-01
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:15
Joined
Sep 21, 2011
Messages
8,752
Assuming 10 characters and the table name is PN, original part number in field PNum:

Code:
SELECT PN.PNum, Left([PNum],10) AS NewPartNo
FROM PN
WHERE (((PN.PNum) Like "90*"));
That will not work for those entries beginning with Cut?
 

Mike Krailo

Active member
Local time
Today, 09:15
Joined
Mar 28, 2020
Messages
310
I thought he said only numbers that start with 09? Maybe I misunderstood what he was asking. I need to learn more on those regex patterns as that can really be quite powerful.
 

Users who are viewing this thread

Top Bottom