Solved

Extract Characters From String Based On Format (Part 2)

Posted on 2011-03-08
8
239 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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
 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
trouble referencing field on sub form 8 37
Sum in Split Form 17 28
append macro insert 5 corresponding rows for each record 2 21
Menu Macro ‘Action Failed’, Access 2003 7 18
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

830 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