Extracting sub-string from excel column before performing match with another column (VBA)

[javatyphoon]

I am fairly new to VBA and have been working on a task to automate a section of project report using VBA. The ultimate aim is to update the status of each test step in an MS Word Table with the latest test results. I have a working subroutine that imports the word tables to an excel worksheet (sheet 2). Sheet 1 already contains an excel table with the latest test results. My plan is to match items in the two sheets using the common identifier "TP_ID", however the appropriate column in the imported word table also contains a section number that is stopping any matches being found (see images below to see format of each sheet ID column).

TP_ID.PNG
Section_TP_ID_2.PNG


I would like to find a way to either extract the TP_ID substring from the word table column and then perform a match routine. Below is the code I have so far. You can see the crucial step that I am missing marked by '????'

Once matching is of all 2000 odd test steps is complete I then want to update column D of the imported word table on sheet 2 with the latest test results contained within column G of sheet 1. I believe my code reflects this, however I cannot test due to the fact that no matches can be found without the step that extracts TP_ID from the combined section number and TP_ID string.


code_2.PNG

I am aware of the RIGHT, LEFT and MID, routines but I have not found a way to use these because the length of the section number varies.


Any assistance would be greatly appreciated

[Haluk]

Maybe like this;

Please Login or Register  to view this content.

[jindon]

I am aware of the RIGHT, LEFT and MID, routines but I have not found a way to use these because the length of the section number varies.

e.g

Please Login or Register  to view this content.

[Rick Rothstein]

Assuming you ID numbers (after the dash) are always 5 digits (could have non-digits in there as well) long, which appears to be the case given your examples, then this code will work...

Please Login or Register  to view this content.

Note: This code will work for the ID's that stand alone as well

Please Login or Register  to view this content.