On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.
Course Of The Month
6 days, 21 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Extract Characters From String Based On Format
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:
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
From line 1 I would be extracting 1485, line 2 1239 and so on.
Your help would be much appreciated.
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
From line 1 I would be extracting 1485, line 2 1239 and so on.
Your help would be much appreciated.
[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
8-
3
2
13 Comments
This should do what you're after:
SELECT YourField, Mid(YourField, InStrRev(YourField, "-") - 4, 4) AS Code
FROM YourTable
Mmmm, on second thoughts that won't work with this record
10-421 Sales-Training 1215- Tra Fees - Gen
From the examples you've cited, you'll have to create a VBA function to parse the text, and invoke this from your query.
10-421 Sales-Training 1215- Tra Fees - Gen
From the examples you've cited, you'll have to create a VBA function to parse the text, and invoke this from your query.
Can you confirm that the record values in your question are EXACTLY as you have listed them - including spaces and hyphens.
Once you've done this I can be confident that all cases are covered.
Once you've done this I can be confident that all cases are covered.
In particular, can you confirm if this record:
10-413 Sales- Creative 1239- Specs UK advert
should actually be:
10-413 Sales-Creative 1239- Specs UK advert
10-413 Sales- Creative 1239- Specs UK advert
should actually be:
10-413 Sales-Creative 1239- Specs UK advert
Also, are there supposed to be two spaces before the code you're trying to extract in some records or is this just a typo?
Use this function in a query to extract the number.
It assumes the number is terminated with a "-".
Select *, getnos(Desc) from tablename
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
It assumes the number is terminated with a "-".
Select *, getnos(Desc) from tablename
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
If your examples are correct, then you can extract the code by first creating the following function:
Option Explicit
Option Compare Database
Public Function ExtractCode(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
ExtractCode = Mid(pstrText, bytCharacter, 4)
End Function
You can then use the function in a query like this:
SELECT YourField, ExtractCode(YourField) AS Code
FROM YourTable
Hi Jez, the leading spaces are contained in the csv file I import to begin the process. I can trim them so please consider the string minus the leading spaces.
Each line may vary as to the spaces contained in it, and the number of hyphens. The one consistent element is the ####- in the latter part of the string. The example you mentioned above does have an extra space behind Sales-. That will have been a user typo when the code string was created and isn't something I can alter.
So, I need to extract every string component formatted as ####-. I hope this helps.
Each line may vary as to the spaces contained in it, and the number of hyphens. The one consistent element is the ####- in the latter part of the string. The example you mentioned above does have an extra space behind Sales-. That will have been a user typo when the code string was created and isn't something I can alter.
So, I need to extract every string component formatted as ####-. I hope this helps.
Oops, just found a slight complication. Occasionally the code within the string is formatted as ####-####.
e.g. 2588-4876
In those cases I need the first four, and the second four.
Peter57r, can your code be modified/copied to split off the numbers on the right?
e.g. 2588-4876
In those cases I need the first four, and the second four.
Peter57r, can your code be modified/copied to split off the numbers on the right?
Use this to get either pattern:
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
If Format(Val(Mid(pstring, x + 1, 4)), "0000") = Mid(pstring, x + 1, 4) Then res = res & "-" & Mid(pstring, x + 1, 4)
End If
GetNos = res
End Function
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
If Format(Val(Mid(pstring, x + 1, 4)), "0000") = Mid(pstring, x + 1, 4) Then res = res & "-" & Mid(pstring, x + 1, 4)
End If
GetNos = res
End Function
Or like this:
Option Explicit
Option Compare Database
Public Function ExtractCode(pstrText As String) As String
Dim bytCharacter As Byte
Dim bytLength As Byte
For bytCharacter = 7 To Len(pstrText)
If IsNumeric(Mid(pstrText, bytCharacter, 1)) Then
If IsNumeric(Mid(pstrText, bytCharacter + 5, 1)) Then
bytLength = 9
Else
bytLength = 4
End If
Exit For
End If
Next
ExtractCode = Mid(pstrText, bytCharacter, bytLength)
End Function
Featured Post
Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month6 days, 21 hours left to enroll
726 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.