I've attached an updated version of the test file to show the outcome of the last suggestion -- and provided explanation in the blue message block..
That 1st formula is better it seems than the one I had, but still think a VBA solution using the IF THEN route w/ 5 to 10 rule variations will likely be the most dynamic and dependable...(with the least amount of manual edits) when a variety of file names from different mainframe systems gets pasted into Col A (see attachment to see what I mean).
Alternatively, if there's a way to use VBA to use that lookup table to lookup the matching LEN in col N and then PASTE the formula found in col O into col K, then the user could quickly add variations without having to go into the backside to add lines of code.
I'm find with either method, whichever is easier -- as I'll be the one using it most -- but was thinking ahead -- in the event I had to pass this to a client to use on their own who didn't know anything at all about VBA... (I'm not an expert by any means - but can do basic things)
Thanks again for your help! Look forward to seeing if those 2 VBA options might work?
|