VBA to Extract text with certain pattern from a string within a single cell

  • Dear All, I have a string contained in a single cell as below.



    I need a way to extract the text 50-PG-103-6101X, 100-D-105-6101X, 150-BD-101-6101X, 150-PG-127-6101X and 80-PG-142-61430 into another cell. Preferably in this format.


    50-PG-103-6101X
    100-D-105-6101X
    150-BD-101-6101X
    150-PG-127-6101X
    80-PG-142-61430



    I tried searching all over the net, but none of the solution can do it ( or maybe I'm using the wrong search term). Hope you guys can point me at the right direction.

  • Re: VBA to Extract text with certain pattern from a string within a single cell


    Can you please post a sample that has this information? Your sample has in common that the string you want is always 1 space after ) and 1 space before (. So that you might help yourself the mid() function will be of use to extract the string you want.

  • Re: VBA to Extract text with certain pattern from a string within a single cell


    The rest of my data not necessarily as the example above. But what is consistent in the format of the text I want to extract.


    ###-TT-###-####T


    # is a number
    T is an alphabet


    There is always 3 dash/hyphen within that text. For those that is familiar, that is a piping line number..

  • Re: VBA to Extract text with certain pattern from a string within a single cell


    The rest of my data not necessarily as the example above. But what is consistent in the format of the text I want to extract.


    ###-TT-###-####T


    # is a number
    T is an alphabet


    There is always 3 dash/hyphen within that text. For those that is familiar, that is a piping line number..

  • Re: VBA to Extract text with certain pattern from a string within a single cell


    see if this works. FYI your sample includes strings that are not consistent with the pattern you say should exist.


  • Re: VBA to Extract text with certain pattern from a string within a single cell


    Quote from bryce;789634

    see if this works. FYI your sample includes strings that are not consistent with the pattern you say should exist.



    Hi Bryce,


    Thanks for the feedback.


    You are right on the pattern, they are not consistent as per my explanation. My intention was to get a solution for ###-TT-###-####T then try my hand in changing it to fit the rest of the pattern.


    I will give the code a try and let you know how it goes with the real samples.



    Update:
    I think my explanation was a bit vague, the entire text actually contained in a single cell which mean we need to manipulate the data on string level (e.g The entire quoted text is in cell A1) , one of the reason why I was stuck since I'm not familiar with string manipulation.. Your example consider each line is kept in an individual cell. Should looks like this, as shown in the photo below. (I tried attaching file but seem I have issue with the forum attachment system)


    [Blocked Image: https://pictr.com/images/2017/04/04/96a72a9978c5e963a26240d5ae4496f1.jpg]

  • Re: VBA to Extract text with certain pattern from a string within a single cell


    Ok, please post an excel file so I can see how it looks and give you a better solution. That pic looks like there are carriage returns to produce that spacing. Initially I thought the data was in cells and placed the part # in the adjacent cell but if there is only one long string where do you want the part numbers? If you already have that in mind please include that in the sample. Again if you do so, I can give you a better solution.

  • Re: VBA to Extract text with certain pattern from a string within a single cell


    Great. About the upload. When you are replaying back, click "Go Advanced" in the bottom right. Scroll to about the middle of the page that pops up and choose "Manage Attachments." Another smaller window will appear, click "Add Files" From here navigate to your file and double click or click Open. Shen click thge button to "Upload File. At this point you are done. Just choose the "Done" botton and your file will be included in the post.

  • Re: VBA to Extract text with certain pattern from a string within a single cell


    here you go. I changed the pattern a little. Instead of looking for 4 numbers and 1 character at the end of the part number I simply look for 5 numbers or letters. There is also no need to remove the item No. xxx or the numerical listing in front of the part number. This returns only the part number


  • Re: VBA to Extract text with certain pattern from a string within a single cell


    Quote from bryce;789740

    here you go. I changed the pattern a little. Instead of looking for 4 numbers and 1 character at the end of the part number I simply look for 5 numbers or letters. There is also no need to remove the item No. xxx or the numerical listing in front of the part number. This returns only the part number



    Dude. This is impressive!The amount of code is way less than I have in mind when I plan to do it before. Thanks!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!