• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

Extract Characters From String Based On Format (Part 2)

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
jonlake
Asked:
jonlake
  • 5
  • 3
1 Solution
 
JezWaltersCommented:
Do you want the first and second codes as separate fields?
0
 
jonlakeAuthor Commented:
Yes please, if at all possible
0
 
JezWaltersCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JezWaltersCommented:
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
 
JezWaltersCommented:
How's that for you?
0
 
jonlakeAuthor Commented:
I will be able to try this tomorrow back in the office.
0
 
jonlakeAuthor Commented:
Thanks Jez, great work!
0
 
JezWaltersCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now