Solved

Extract Characters From String Based On Format

Posted on 2011-03-08
13
298 Views
Last Modified: 2012-05-11
Hi, using a query I need to be able to extract a code from within a character string. There can be several thousand lines, each with a different code but the format is always the same ####-. Here is an example:

Desc
10-403 Sal-RecruitInit 1485- Staff Recharge
10-413 Sales- Creative 1239- Specs UK advert
10-415 Marketing Man   1239- Specs UK advert
10-416 SaleProfPublica 1152- Professional Ma
10-416 SaleProfPublica 1153- Optom Career Pa
10-416 SaleProfPublica 1938- ODP Income
10-416 SaleProfPublica 1947- Nat Sem Inc
10-417 Sales - Recall  1239- Specs UK advert
10-421 Sales-Training  1048- Emplymnt Law Ma
10-421 Sales-Training  1049- Rectmnt Mat Inc
10-421 Sales-Training  1161- SmartBus RS In
10-421 Sales-Training  1215- Tra Fees - Gen
10-421 Sales-Training  1327- Conducting Staf

From line 1 I would be extracting 1485, line 2 1239 and so on.

Your help would be much appreciated.
0
Comment
Question by:jonlake
  • 8
  • 3
  • 2
13 Comments
 
LVL 17

Expert Comment

by:JezWalters
ID: 35067121
This should do what you're after:
SELECT YourField, Mid(YourField, InStrRev(YourField, "-") - 4, 4) AS Code
FROM YourTable

Open in new window

0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35067128
Mmmm, on second thoughts that won't work with this record

    10-421 Sales-Training  1215- Tra Fees - Gen

From the examples you've cited, you'll have to create a VBA function to parse the text, and invoke this from your query.
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35067133
Can you confirm that the record values in your question are EXACTLY as you have listed them - including spaces and hyphens.

Once you've done this I can be confident that all cases are covered.
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35067146
In particular, can you confirm if this record:

    10-413 Sales- Creative 1239- Specs UK advert

should actually be:

10-413 Sales-Creative 1239- Specs UK advert

0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35067161
Also, are there supposed to be two spaces before the code you're trying to extract in some records or is this just a typo?
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 35067183
Use this function in a query to extract the number.

It assumes the number is terminated with a "-".

Select *, getnos(Desc) from tablename



Function GetNos(pstring As String) As String
Dim x As Integer, ln As Integer, ch As String
Dim res As String
GetNos = ""
ln = Len(pstring)
If ln < 7 Then Exit Function
For x = 6 To ln
    ch = Mid(pstring, x, 1)
    If ch >= "0" And ch <= "9" Then
        res = res & ch
    ElseIf Len(res) = 4 And ch = "-" Then
       
        Exit For
    Else
        res = ""
    End If
   
Next x
If Len(res) = 4 Then GetNos = res
End Function
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 17

Expert Comment

by:JezWalters
ID: 35067191
If your examples are correct, then you can extract the code by first creating the following function:
Option Explicit
Option Compare Database

Public Function ExtractCode(pstrText As String) As String

    Dim bytCharacter As Byte

    For bytCharacter = 7 To Len(pstrText)
        If IsNumeric(Mid(pstrText, bytCharacter, 1)) Then
            Exit For
        End If
    Next
    ExtractCode = Mid(pstrText, bytCharacter, 4)

End Function

Open in new window

0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35067193
You can then use the function in a query like this:
SELECT YourField, ExtractCode(YourField) AS Code
FROM YourTable

Open in new window

0
 

Author Comment

by:jonlake
ID: 35067203
Hi Jez, the leading spaces are contained in the csv file I import to begin the process. I can trim them so please consider the string minus the leading spaces.

Each line may vary as to the spaces contained in it, and the number of hyphens. The one consistent element is the ####- in the latter part of the string. The example you mentioned above does have an extra space behind Sales-. That will have been a user typo when the code string was created and isn't something I can alter.

So, I need to extract every string component formatted as ####-. I hope this helps.
0
 

Author Closing Comment

by:jonlake
ID: 35067218
Fantastic!

When I grow up I hope to be that good!

Have a great day.
0
 

Author Comment

by:jonlake
ID: 35067827
Oops, just found a slight complication. Occasionally the code within the string is formatted as ####-####.

e.g. 2588-4876

In those cases I need the first four, and the second four.

Peter57r, can your code be modified/copied to split off the numbers on the right?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35068851
Use this to get either pattern:

Function GetNos(pstring As String) As String
Dim x As Integer, ln As Integer, ch As String
Dim res As String
GetNos = ""
ln = Len(pstring)
If ln < 7 Then Exit Function
For x = 6 To ln
    ch = Mid(pstring, x, 1)
    If ch >= "0" And ch <= "9" Then
        res = res & ch
    ElseIf Len(res) = 4 And ch = "-" Then
       
        Exit For
    Else
        res = ""
    End If
   
Next x
If Len(res) = 4 Then
    If Format(Val(Mid(pstring, x + 1, 4)), "0000") = Mid(pstring, x + 1, 4) Then res = res & "-" & Mid(pstring, x + 1, 4)
End If
 GetNos = res
End Function
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35069351
Or like this:
Option Explicit
Option Compare Database

Public Function ExtractCode(pstrText As String) As String

    Dim bytCharacter As Byte
    Dim bytLength As Byte

    For bytCharacter = 7 To Len(pstrText)
        If IsNumeric(Mid(pstrText, bytCharacter, 1)) Then
            If IsNumeric(Mid(pstrText, bytCharacter + 5, 1)) Then
                bytLength = 9
            Else
                bytLength = 4
            End If
            Exit For
        End If
    Next
    ExtractCode = Mid(pstrText, bytCharacter, bytLength)

End Function

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now