Solved

Extract Characters From String Based On Format (Part 2)

Posted on 2011-03-08
8
237 Views
Last Modified: 2012-08-13
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
30-537 Gen Exp - PA Co 3008-0008 Travel-Mileage

From line 1 I would be extracting 1485, line 2 1239 and so on. Occasionally the code within the string is formatted as ####-#### (as in the last example).

e.g. 2588-4876

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

The following code works well to extract the first four characters:

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
Comment
Question by:jonlake
  • 5
  • 3
8 Comments
 
LVL 17

Expert Comment

by:JezWalters
ID: 35068764
Do you want the first and second codes as separate fields?
0
 

Author Comment

by:jonlake
ID: 35068808
Yes please, if at all possible
0
 
LVL 17

Accepted Solution

by:
JezWalters earned 500 total points
ID: 35068814
If so, you could create separate functions to extract each code, like this:
Option Explicit
Option Compare Database

Public Function ExtractCode1(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
    ExtractCode1 = Mid(pstrText, bytCharacter, 4)

End Function

Public Function ExtractCode2(pstrText As String) As String

    Dim bytCharacter As Byte

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

End Function

Open in new window

0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35068819
You can invoke the functions from a query like this:
SELECT YourField, ExtractCode1(YourField) AS Code1, ExtractCode2(YourField) AS Code2
FROM YourTable

Open in new window

0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 17

Expert Comment

by:JezWalters
ID: 35068821
How's that for you?
0
 

Author Comment

by:jonlake
ID: 35072617
I will be able to try this tomorrow back in the office.
0
 

Author Closing Comment

by:jonlake
ID: 35080335
Thanks Jez, great work!
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35083593
No offense to peter57r, but where there's a choice it's usually best to go with the solution that works that also has the least code (see your related question).  Entia non sunt multiplicanda praeter necessitatem - in other words, less is more!  ;-)

Anyway, glad I could be of assistance!  :-)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

911 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

22 Experts available now in Live!

Get 1:1 Help Now