how to copy a certain part of a file name in excel cell

[Megatronixs]

Hi all,

I have an excel file where I run first a macro to get all the files that are in a folder into the excel sheet.
This way I see all the files that are in that folder. All parts of the name are separated with an underscore:

1234_sample_text_here_1234543_123_1234

What I'm interested to copy from that text is the third part starting from the right (this will be always the same). So in the above case I would need to copy the: 1234543
the last 2 parts could change in the length of the text/numbers.

If some one has some Ideas how to find that part of the file name would be great. Maybe a loop to go trough them ;-)

Greetings.

[TMS]

One way:

Please Login or Register  to view this content.

Regards, TMS

[Marc L]

Please Login or Register  to view this content.

Edit : a bit late …

[Megatronixs]

Hi Marc L,

This means I need to pass first the text in the macro to tell him what to look for.
Is this possible to have to macro look in a column and then copy that part to a different cell?

Greetings.

[jindon]

Assuming data is in col.A

Please Login or Register  to view this content.

[Megatronixs]

Hi jindon,

It works great ;-)
I only saw now that in a few cases it happened that they had no 3rd from the right number. but then ther appears text and I could run a macro at the end to take out the ones with that is no number.

Thanks a lot for your great help (all of you).

Greetings.

[jindon]

Then change the pattern to

Please Login or Register  to view this content.

It only extracts if the 3rd from the last is numeric.

[TMS]

This means I need to pass first the text in the macro to tell him what to look for.
Is this possible to have to macro look in a column and then copy that part to a different cell?

No, those examples were just using a text string to demonstrate how the third from the right value could be extracted. You didn't provide a sample workbook or give any indication of where the data was, nor where you wanted the output to go.

No big deal to make it loop. But you have an alternative solution already.

Regards, TMS

[Megatronixs]

Hi jindon,

Works really great the last solution ;-)

Hi TMShucks, thank you for your comment. I actually did not expect it would be solved so easily and was not yet thinking where the output would be or how it would look like. Now that I'm able to take the part out of it, I can start thinking how to integrate this in a bigger macro.

Thank you all for your great help.

Greetings.

[TMS]

You're welcome. Thanks for the rep.