Extract Specific Text from a Cell

  • I am working on a project with VBA and wondering if what I am trying to do is possible. There is only a single field of notes in our database that can be entered and then exported to a standard excel sheet. From this exported excel sheet I want to break up that single note box into a few different rows. Below is an example of the current data that will be exported to excel.


    Pending John Smith // DRWG COOR: Pending ~ // UTILITY COOR : Pending ~ // N1 Upload: Pending ~ // Notes: SUBMITTED PWR APP 06/04/2021


    In VBA I want to extract data from this text (all currently inside a single excel cell) and separate it into different rows. The goal would be to have four new columns added for the following:


    DRWG COOR

    UTILITY COOR

    N1 Upload

    Notes


    With these new columns, I was wondering if you could extract text that directly follows desired row name (DRWG COOR, N1 Upload, etc.). I currently added the // and ~ as ways to possibly help separate the single cell.


    Is this something VBA could do, be able to pull out specific text that follows a certain string of characters but also end before a different designated string of characters. I am still learning VBA coding but from my past experiences I have done some things similar to this, but those were only extracting certain things from a cell, not multiple specific strings of text from a cell.


    I apologize for the long and possibly confusing explanation. I will gladly help answer more questions if needed. Any assistance is much appreciated

  • Try the attached, click the button on sheet1, result will be on Sheet2


    Code assigned to button:

    Files

    • Oz_Chen.xlsm

      (18.77 kB, downloaded 17 times, last: )

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Have an empty Sheet2 to receive result. If not, change the Sheet2 reference to whatever it needs to be.