|
04-13-2020, 07:04 PM
|
 |
Expert
|
|
Join Date: May 2013
Location: USA
Posts: 700
|
|
It's probably easiest for you, and for me too, to work on your example and upload my own solution here. Meanwhile, while I'm working on that: I mentioned before that a simpler solution might be to ignore the file length. In each case, it seems to me, your file names are a bunch of characters (of varying length), followed by "nnnnn.TXT". And what you want in each case is the nnnnn part. That being the case, why worry about the length of the filename? Just take the five numeric characters that come before ".TXT", like this:
Code:
=LEFT(RIGHT(A2,9),5)
That one formula works no matter how long your filename is; no need for a VBA program at all.
I mentioned this before and you said there are other filename variations for which that wouldn't work. But I don't see any exceptions in your sample worksheets; I suspect you just didn't notice how the above formula works. Take another look.
Of course it's possible there will be other variations that you haven't told me about, or maybe even you're just keeping in mind that you cannot know what variations may come up and you'd rather stay flexible. But in that latter case it seems to me you have no guarantee that filename length will be the right determinant. I'm very distrustful of assuming this is all going to go on working forever using filename length. Can you tell me more about the possible variations? There are other approaches.
|