Printable View
I am trying to figure out how to extract a string of characters from a field. They all start with 09 and are 10 characters long. They could be anywhere in the field beginning middle or end.
example of data in the field
CUT 2 (09GN20T-01) LEADS (09BN22R-01) CUT 3.5" CUT 2 (09GN20T-01) LEADS 09BN22R-01 CUT 3.5" WITH CUT 09BN22R-01 1.50" LONG 1
So the result I am looking for is
DESC 09GN20T-01 09BN22R-01 09GN20T-01 09BN22R-01 09BN22R-01
Any help with this would be much appreciated!
Try this formula
=MID(A2,FIND("09",A2),10)
Its says unidentified function FIND in expression
Is this Access or Excel?
As I think Bob's answer is an Excel equation.
It can be found quite easily using VBA using the instring function or by parsing the whole data a pair od characters at a time.
You can also Left, Right and Mid string functions to get the whole 10 characters.
I have posted a few versions of old fashioned BASIC use, on is the post pinned to the top of the Forum.
I can assist you with the VBA code but I woud need more info.
1. is this during an import?
2. How many records in the table need modifying.
3. Do you need to modify new records.
4. Do you want to this this automatically or on a command button.
Apologies, looked in today's posts, didn't even notice it was Access forum.Quote:
Originally Posted by OBP
This is an access query.
I do not need to modify any records I just need to extract the data so that I only have the 09* Part #s in a field so that I can use that field to link to a different table.
There are about 14,000 records that I need to pull the 09* out of.
I was a little confused when attempting to use Left, Right and Mid because the 09 can be at the beginning middle or end of the data.
OK, I can help you.
I need to know the name of the table holding the data to parse, the name of the field that holds the string of data.
The name of the table and field where you want the data to go.
Useful would be the Access version as you may have to change some VBA editor Library References to get it to work as I am using Access 2007.
A dummy database with some of the data in it and the table where you want it to go would be very good, providing it is in Access 2000-2007 format and does not contain any private personal data.
The code for the parsing is simple.
This is the code on a form button to take the string from Field1, parse it and put it in the field result.
I just need to add a recordset to get the data and a loop to iterate through the records.Code:Dim datastring As String, x As Integer
datastring = Me.Field1
x = InStr(datastring, "09")
'MsgBox x
If x > 0 Then Me.result = Mid(datastring, x, 10)
Name of the table - routing_steps
field name in routing_steps - DESC
if possible I would want it in the same table
table I want it to go - routing_steps
field - 09DESC
OK, I will post something tomorrow as I am busy right now.
No problem. Thank you for the help. This has been driving me crazy!
I had a bit of time to spare so I went ahead and created it for you.
This database has your table with the 2 fields.
There is a form that is opened by an Autexec Macro.
Note that the table and form records have nothing in the 09DESC field.
Click the button and it will populate the table and requery the form.
You do not need to have the records on the form, they are only there to save you opening the tabel to see the changes.
You only need the button and it's code on a form
Thank you so much this is exactly what I was looking for. I went through the code to learn how you did it as well so that was a big help. Next time I need to do something like this I will know how to do it.
Thanks again for all the help!
Crossposted https://www.access-programmers.co.uk.../#post-1769734
And solutions offered on the 4th June?????
So why are you stirring it on both forums?
I just posted that they had crossposted and due to the timing of the crossposting, I also highlighted the fact that they did it even after getting several options to get what they were asking for.? After all there were 29 posts on the other forum?Quote:
Originally Posted by OBP
The O/P could go back and ask for more help from that other forum (or here even?), so I feel it is only fair, that people who help out, are aware that what they could be offering a solution that has already been offered.? If you only advise of cross posting in one forum, the other forum is not aware?
on the other forum, the op said it could be More than 10 chars (part no.).
on the future we don't know what it will contain.
can be (09)(09)09 (09BN22R-01) CUT 3.5
so the solution offered here cannot deal with it.
it must handle any worst case scenario.
I provided the solution to the Data the OP provided to be analysed, so you should take it up with the OP.
I provided the solution to the Data the OP provided to be analysed, so you should take it up with the OP.
As he declared himself satisfied with the soultion I provided why don't you clear off back to your own forum.
ps you are also wrong, he clarified the length here
On further review it looks like they are all 10 characters long and will always end with two numbers at the end