1

As part of a bigger code I am trying to split a file name string so that I can use the base number to search for a file in a set of folders.

The typical file names are:

TEST-999-TY-MOD-DOC-000999-SOME RANDOM FILE NAME.xls TEST-999-TY-MOD-DOC-000998_SOME RANDOM FILE NAME.xls TEST-999-TY-MOD-DOC-000997 SOME RANDOM FILE NAME.xls TEST-999-TYP-MOD-DOC-000999-SOME RANDOM FILE NAME.xls TEST-999-TYP-MOD-DOC-000998_SOME RANDOM FILE NAME.xls TEST-999-TYP-MOD-DOC-000997 SOME RANDOM FILE NAME.xls

The only thing in those file names that doesn't change is the 6 digit format in the 6th section Is there a way that I can use the split function on all of these file names to only have the base name of

TEST-999-TY-MOD-DOC-000999

If they were all the same length then I could use the left function but because it changes and they are not consistant with what they use further down the file name i'm struggling

cheers

Improve this question

1 Answer 1

Reset to default
3

We can try doing a regex replacement with the following pattern:

^(.*-\d{6}).*$

Then, we can replace with the first (and only) capture group, thereby removing the filename at the end.

Dim RE As Object Set RE = CreateObject("VBScript.RegExp") RE.ignoreCase = True RE.Global = True RE.Pattern = "^(.*-\d{6}).*$" Debug.Print RE.Replace("TEST-999-TY-MOD-DOC-000999-SOME RANDOM FILE NAME.xls", "$1")

Here is a demo showing that the regex is working correctly:

Demo

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I didn't even know you could even use regedit in VBA. It works perfect on the first type but how would i expand it to include the file name with the underscore or the space ? and thank you
The pattern works for all your cases, at least the ones you showed us. Basically it regex substrings off everything after the sequence of six digits. Check the regex demo link if you are in doubt.
sorry i took your first edit and ran with it. works like a dream. been on it for a while and could never quite get it right within VBA but that is superb cheers.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.