Now that I look at your worksheet I see I misunderstood your request. This is one reason I grouse about A1 format: A poster says "A2", and I can't tell whether he means that row particularly or just "the current row". We need a convention like "Ar" (or something) to mean "the current row, column A".



R1C1 is better. But I don't really hope, realistically speaking, to get everyone used to saying "RC1" instead of "$Ar". It is much less ambiguous, though.

Back to your question: Now that I see the actual formulae over in column O, I realize what you're trying to do. In that case, I'm still sure you can do it with VBA and "remote" formulae—to coin a term—but it's easier this way: In column K try "=LEFT(RIGHT(A2,9),5)" (without the quotes). You don't need to check the length of the name in column A; all you need, unless there's more variation in the filenames than shows here, is to take the rightmost nine characters of the filename, and then the leftmost five characters of the result. If you need the results to be displayed as a number instead of a character string, just add 0 ("=LEFT(RIGHT(A2,9),5)+0"). Copy that formula down col K and see whether it isn't what you want.

Do you still want to do it in VBA, or at least to know how? If so, we can work on that too. It would be useful, if some filenames differ from the pattern I see here.

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?

Attached Files

test-paste-formula.xlsm (21.7 KB, 8 views)