472,126 Members | 1,602 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,126 software developers and data experts.

Extract from string

24 16bit
Hi All
I have a VBA code in access which I am using to split a string and inserting the results to a table.
the string will have alphabets and numbers. If the first 3 characters are alphabets and the 4th character is a number I need to extract the first 3 characters. And if the string has all alphabets or numbers or numbers before 3rd characters, insert the same string to the field. But if the string has 3 alphabets, then numbers and then alphabets, I need only the first 3 alphabets extracted to my table. My VBA code is not doing this. Hope someone can guide me what I am missing. Please note that the code I got was from internet and modified to accommodate my use.
Expand|Select|Wrap|Line Numbers
  1.  Dim OldString As String
  2.     Dim NewString As String
  3.     Dim rstAlpha As Recordset
  4.     Dim rstAlphaNum As Recordset
  5.     Dim I As Integer
  6.     DoCmd.RunSQL "Delete * from ResultTbl"
  7.  
  8.     Set rstAlpha = CurrentDb.OpenRecordset("ResultTbl", dbOpenDynaset)
  9.     Set rstAlphaNum = CurrentDb.OpenRecordset("MyDataTbl", dbOpenDynaset)
  10.  
  11.     rstAlphaNum.MoveFirst
  12.  
  13.     Do While rstAlphaNum.EOF = False
  14.         OldString = rstAlphaNum![MyStringitem]
  15.         NewString = ""
  16.         For I = 1 To Len(OldString)
  17.  
  18.  
  19.          If Not IsNumeric(Mid(OldString, I, 4)) Then
  20.              NewString = NewString & (Mid(OldString, I, 1))
  21.  
  22.           End If
  23.         Next I
  24.         rstAlpha.AddNew
  25.         rstAlpha![ExtractedString] = NewString
  26.         rstAlpha.Update
  27.         rstAlphaNum.MoveNext
  28.     Loop
  29.     MsgBox "Finished"
  30.     'DoCmd.OpenTable "ResultTbl", acViewNormal
Jan 6 '21 #1

✓ answered by cactusdata

  1. If the first 3 characters are alphabets and the 4th character is a number I need to extract the first 3 characters.
  2. If the string has all alphabets or numbers or numbers before 3rd characters, insert the same string to the field.
  3. If the string has 3 alphabets, then numbers and then alphabets, I need only the first 3 alphabets.
1 and 3 are the same, so you can cut it down to check for a digit in the first three characters.
If found, pull the full string; if not pull the first three letters if the fourth is a digit:

Expand|Select|Wrap|Line Numbers
  1. Public Function ExtractThreeOrMore(ByVal Value As String) As String
  2.  
  3.     Dim Position    As Integer
  4.     Dim Result      As String
  5.     Dim DigitExists As Boolean
  6.  
  7.     If Len(Value) >= 3 Then
  8.         For Position = 1 To 3
  9.             If IsNumeric(Mid(Value, Position, 1)) Then
  10.                 DigitExists = True
  11.                 Exit For
  12.             End If
  13.         Next
  14.     End If
  15.  
  16.     If DigitExists Then
  17.         Result = Value
  18.     Else
  19.         If IsNumeric(Mid(Value, 4, 1)) Then
  20.             Result = Left(Value, 3)
  21.         End If
  22.     End If
  23.  
  24.     ExtractThreeOrMore = Result
  25.  
  26. End Function
Now, use any method to insert the result in your table.

7 4598
isladogs
409 Expert Mod 256MB
Please give some sample data and the required result in each case to clarify what you want.
Jan 6 '21 #2
NeoPa
32,497 Expert Mod 16PB
On similar lines as IslaDogs - What does your question even mean. I've read it a couple of times and all I got was head pain & confusion.

I get that when you refer to alphabets you are actually referring to alphabetic characters (Or more simply just letters.) but the rest was so unclear I gave up.

Before I leave you with just this complaint let me just add that we are probably going to be able to help once we have a decent question to work with. I'd be surprised if the use of the Like comparison didn't come into it somewhere, but more detail than that will depend on the actual requirement.
Jan 7 '21 #3
cactusdata
202 Expert 128KB
  1. If the first 3 characters are alphabets and the 4th character is a number I need to extract the first 3 characters.
  2. If the string has all alphabets or numbers or numbers before 3rd characters, insert the same string to the field.
  3. If the string has 3 alphabets, then numbers and then alphabets, I need only the first 3 alphabets.
1 and 3 are the same, so you can cut it down to check for a digit in the first three characters.
If found, pull the full string; if not pull the first three letters if the fourth is a digit:

Expand|Select|Wrap|Line Numbers
  1. Public Function ExtractThreeOrMore(ByVal Value As String) As String
  2.  
  3.     Dim Position    As Integer
  4.     Dim Result      As String
  5.     Dim DigitExists As Boolean
  6.  
  7.     If Len(Value) >= 3 Then
  8.         For Position = 1 To 3
  9.             If IsNumeric(Mid(Value, Position, 1)) Then
  10.                 DigitExists = True
  11.                 Exit For
  12.             End If
  13.         Next
  14.     End If
  15.  
  16.     If DigitExists Then
  17.         Result = Value
  18.     Else
  19.         If IsNumeric(Mid(Value, 4, 1)) Then
  20.             Result = Left(Value, 3)
  21.         End If
  22.     End If
  23.  
  24.     ExtractThreeOrMore = Result
  25.  
  26. End Function
Now, use any method to insert the result in your table.
Jan 7 '21 #4
jackjee
24 16bit
Hi cactusdata
Thank you for the provided function. I used it in a query and it looks like it is giving expected results
The syntax of query as "SELECT data.alphabets, ExtractThreeOrMore([alphabets]) AS Result
FROM data;"
The sample records in the table as below
Field name: alphabets

1NTRWE
NNB3456
7DCXD
NNB67FX
DUC6
LIY683
LIY9RT
MM2345
LO8765F

And the results I got in the query as below
Field Name: Result
1NTRWE
NNB
7DCXD
NNB
DUC
LIY
LIY
MM2345
LO8765F

I think it is working fine. I need to test it with large data and also need to try how to add this function in my old code.
Thank you so much for interpreting my question correctly and provided a quick solution. 'bytes' always very supportive
Jan 7 '21 #5
cactusdata
202 Expert 128KB
You are welcome! Get back if you have questions.
Jan 7 '21 #6
nhakhoaparis
1 Bit
Wow thanks for share
Jan 8 '21 #7
SioSio
272 256MB
Another way using "RegExp"
Expand|Select|Wrap|Line Numbers
  1. Function ExtractData(ByVal s As String) As String
  2.     Dim RegExp As Object
  3.     Set RegExp = CreateObject("VBScript.RegExp")
  4.     RegExp.Pattern = "^[a-zA-Z]+$"
  5.     If RegExp.test(Left(s, 4)) Then
  6.         ExtractData = s
  7.     ElseIf Not RegExp.test(Left(s, 3)) Then
  8.         ExtractData = s
  9.     Else
  10.         ExtractData = Left(s, 3)
  11.     End If
  12.     Set RegExp = Nothing
  13. End Function
Expand|Select|Wrap|Line Numbers
  1.  s = ExtractData("1NTRWE")
Jan 20 '21 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Sharon | last post: by
12 posts views Thread by rshepard | last post: by
1 post views Thread by josephtys86 | last post: by
1 post views Thread by Edwin.Madari | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.