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:

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

Participate now!

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