Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Extract Characters From String Based On Format (Part 2)

Posted on 2011-03-08
8
Medium Priority
?
249 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

719 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