473,692 Members | 2,470 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Extract from string

24 New Member
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
7 5041
isladogs
460 Recognized Expert Moderator Contributor
Please give some sample data and the required result in each case to clarify what you want.
Jan 6 '21 #2
NeoPa
32,569 Recognized Expert Moderator MVP
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
214 Recognized Expert New Member
  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 New Member
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, ExtractThreeOrM ore([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
214 Recognized Expert New Member
You are welcome! Get back if you have questions.
Jan 7 '21 #6
nhakhoaparis
1 New Member
Wow thanks for share
Jan 8 '21 #7
SioSio
272 Contributor
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

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

Similar topics

9
16969
by: Sharon | last post by:
hi, I want to extract a string from a file, if the file is like this: 1 This is the string 2 3 4 how could I extract the string, starting from the 10th position (i.e. "T") and extract 35 characters (including "T") from a file and then go to next line?
4
2936
by: Chris | last post by:
I am calling a COM object that returns a type Object and it contains two strings but I can't get them out. Here is the command: object ParamNameObj= localxPCCOMOBJ.xPCTarget.GetParamName (1); ParamNameObj shows up as a System.Array in the watch window with the following elements. - datastr1 as string
12
4153
by: rshepard | last post by:
I'm a bit embarrassed to have to ask for help on this, but I'm not finding the solution in the docs I have here. Data are assembled for writing to a database table. A representative tuple looks like this: ('eco', "(u'Roads',)", 0.073969887301348305) Pysqlite doesn't like the format of the middle term: pysqlite2.dbapi2.InterfaceError: Error binding parameter 1 - probably
8
2478
by: zadkiel | last post by:
hi all. I got a urgent problem in my job. the sample data as follows: FT="EXERPRI:$68.88/10W*BB9505-28765710" FT="MEETON6/3/07FOR What I need is to extract the string after the quote and before any punctuation or integer so my outcome can be like this:
1
1962
by: AccessHunter | last post by:
Hi, Please help as this is urgent. I have the following data in a column. In the report I want to display them only with whatever is before the "-". A SECOND DAY, INC.-FORMAT 4 A SECOND ADY, INC-VERSION 1 A SECOND DAY,INC.-VERSION 1 CLAIR FOUNDATION - OPTION 1
1
1524
by: josephtys86 | last post by:
203.114.10.66 - - "GET /stat.gif? stat=v&c=F-Secure&v=1.1%20Build%2014231&s=av%7BNorton %20360%20%28Symantec%20Corporation%29+69%3B%7Dsw%7BNorton %20360%20%28Symantec%20Corporation%29+69%3B%7Dfw%7BNorton %20360%20%28Symantec%20Corporation%29+5%3B%7Dv%7BMicrosoft%20Windows %20XP+insecure%3BMicrosoft%20Windows%20XP%20Professional+f %3B26027%3B26447%3B26003%3B22452%3B%7D&r=0.9496 HTTP/1.1" 200 43...
1
2720
by: Edwin.Madari | last post by:
from each line separate out url and request parts. split the request into key-value pairs, use urllib to unquote key-value pairs......as show below... import urllib line = "GET...
0
1675
by: javaBookWorm | last post by:
203.114.10.66 - - "GET /stat.gif?stat=v&c=F-Secure&v=1.1%20Build%2014231&s=av%7BNorton%20360%20%28Symantec%20Corporation%29+69%3B%7Dsw%7BNorton%20360%20%28Symantec%20Corporation%29+69%3B%7Dfw%7BNorton%20360%20%28Symantec%20Corporation%29+5%3B%7Dv%7BMicrosoft%20Windows%20XP+insecure%3BMicrosoft%20Windows%20XP%20Professional+f%3B26027%3B26447%3B26003%3B22452%3B%7D&r=0.9496 HTTP/1.1" 200 43...
6
2374
by: artemetis | last post by:
Hi there! I have a txt file with a string of about 1,000 ip addresses, delimited by comma. I need to read the file into Access (just to have it displayed in a qry for view. I have the .txt file linked in Access, but how to get each entry (delimited by comma) into a new line item?
0
1214
by: Yudhi | last post by:
Write Regular Expression that can extract String 123.jpg and 432.png from String axhdsjk123.jpg.jpg and hjhsd432.png.png How to...?????????
0
8611
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8970
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8812
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8810
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7639
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6462
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5822
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
2984
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2242
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.