Extract Characters From String Based On Format (Part 2)
Posted on 2011-03-08
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:
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).
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
res = ""
If Len(res) = 4 Then GetNos = res