Consulting

Results 1 to 20 of 20

Thread: Extract characters from a field

  1. #1

    Extract characters from a field

    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!


  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Try this formula

    =MID(A2,FIND("09",A2),10)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Its says unidentified function FIND in expression

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  5. #5
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by OBP View Post
    Is this Access or Excel?.
    Apologies, looked in today's posts, didn't even notice it was Access forum.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    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.

  8. #8
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  9. #9
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.
    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)
    I just need to add a recordset to get the data and a loop to iterate through the records.
    Last edited by OBP; 06-09-2021 at 09:14 AM.

  10. #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

  11. #11
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, I will post something tomorrow as I am busy right now.

  12. #12
    No problem. Thank you for the help. This has been driving me crazy!

  13. #13
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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
    Attached Files Attached Files

  14. #14
    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!

  15. #15
    VBAX Regular
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    86
    Location
    Crossposted https://www.access-programmers.co.uk.../#post-1769734

    And solutions offered on the 4th June?????

  16. #16
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    So why are you stirring it on both forums?

  17. #17
    VBAX Regular
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    86
    Location
    Quote Originally Posted by OBP View Post
    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?

    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?

  18. #18
    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.

  19. #19
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I provided the solution to the Data the OP provided to be analysed, so you should take it up with the OP.

  20. #20
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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
    Last edited by OBP; 06-13-2021 at 02:13 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •