Find and extract substring matching values in array

[Onjslnjps]

I have an array (single column) of filenames that contain IDs that I would like to extract to another column. The filenames have varying length and formats and the IDs can also have slightly differing formats, hence the difficulty matching values. I would like to extract the ID in a uniform format.


For example:


filenames: ------------>extracted IDs:
xxxxR01_A01xxxx --->R01A01
xxP23_H03xxx ------->P23H03
xP3H3xxxxxxx ------->P03H03
xxP23H03_A01xx ---->P23H03


My problem is identifying which part of the filename contains the ID value, since it can change but is generally consistent within each separate dataset. The first three characters and last three characters of the extracted ID value could be matched to values in a known set of possible values if necessary. Any help with either a formula or macro that cold achieve this would be greatly appreciated.

 

[pgc01]

I have an array (single column) of filenames that contain IDs that I would like to extract to another column. The filenames have varying length and formats and the IDs can also have slightly differing formats, hence the difficulty matching values. I would like to extract the ID in a uniform format.


For example:


filenames: ------------>extracted IDs:
xxxxR01_A01xxxx --->R01A01
xxP23_H03xxx ------->P23H03
xP3H3xxxxxxx ------->P03H03
xxP23H03_A01xx ---->P23H03


My problem is identifying which part of the filename contains the ID value, since it can change but is generally consistent within each separate dataset. The first three characters and last three characters of the extracted ID value could be matched to values in a known set of possible values if necessary. Any help with either a formula or macro that cold achieve this would be greatly appreciated.

Hi

These type of problems are usually simple when we have a clear description of the logic involved. This is not the case. Your explanation is incomplete.

Please explain the problem in such a way that anyone can identify without any doubt what is the identifier.

For ex., in your example

- cases 1 and 2 is just to extract strings (if we knew what to extract), and remove the undersores
- case 3 needs extraction of a string and then editing the string
- case 4 seems to be to extract just part of a string (?), In fact you keep the "A01" in case 1 and you don't in case 4

In conclusion, if you want a solution you must

- explain the logic thoroughly
- give a list of examples with input and respective output that cover all the possibilities