How can I extract a specific name out of file name

[Kyletok]

Honestly I did not know how to frame this question because I am not sure what the most correct terminology here should be.

I created a script that renames all files in a defined folder path according to a naming convention list in another tab.
for example,

Inbound	Outbound
ABC.example.txt.****.txt	example.txt

A file named ABC.example.txt.1234.txt will be renamed example.txt. So far is good, and the script works great. But now we have a bit of a new scenario with more variables that I need to consider. I have the following naming convention.

Inbound	Outbound
APX.example_********_********_******.txt.****.txt	example_*.txt

This time the outbound needs to extract the values from inbound.
A file named APX.example_12345678_12345678_123456.txt.1234.txt will be renamed example_12345678_12345678_123456.txt

To simplify my question, what is the best approach to achieve the following results:
Dim oldFile, pattern, newFile
oldFile = APX.example_12345678_12345678_123456.txt.1234.txt
pattern = example_*.txt
newFile = 'DO SOMETHING TO RESULT IN example_12345678_12345678_123456.txt

The code should be valid for:
Dim oldFile, pattern, newFile
oldFile = ABC.example.txt.****.txt
pattern = example_.txt
newFile = 'DO SOMETHING TO RESULT IN example_.txt

any suggestions are appreciated!

 

[kennypete]

It's not entirely clear, but use this for newfile in your first example and it should do it:

newFile = Split(oldFile, ".")(1) + "." + Split(oldFile, ".")(2)

As for the second, a conditional test for whether "example.txt" staning alone/specifically appears could be added.

 

[Kyletok]

This works if it were always to follow that pattern.
I was hoping to find some code that would read through the defined pattern.
oldFile = APX.example_12345678_12345678_123456.txt.1234.txt
pattern = example_*.txt
newFile = example_12345678_12345678_123456.txt

I need the users to be able to define the patterns in the sheet. The macro goes through all files in a folder path, checks if the name is LIKE any of the inbound options, and then renames the file to the corresponding outbound result. It works like a charm when the Outbound is just a static result, however in the second option it needs to pull the red text into the new file name.

I could use a condition that checks if the corresponding outbound has "*" in it to know if to pull the static name or if to use your formula, but there are some instances like the third in the below table, that it wouldn't work for. We can't control the naming of the source files unfortunately, and sometimes they come out in really strange formats.

Inbound	Outbound
ABC.example.txt.****.txt	example.txt
APX.example_********_********_******.txt.****.txt	example_*.txt
XTR.BC_*******_example.****.txt	BC_*_example

essentially I am hoping to have some sort of logic that will extract the Outbound text out of the Inbound text, with the value in * being dynamic.

 

[kennypete]

If the logic you are looking for is to exclude (a) the initial upper case (or whatever) plus the full point and (b) everything after the second to last full point, then this extended code does that.

Sub sub1175712()
    Dim oldFile, newFile
    For n = 1 To 3
        oldFile = Cells(n, 1)
        newFile = ""
        For sn = (UBound(Split(oldFile, ".")) - 2) To 1 Step -1
            If newFile = "" Then
                newFile = Split(oldFile, ".")(sn)
            Else
                newFile = Split(oldFile, ".")(sn) + "." + newFile
            End If
        Next sn
        Debug.Print oldFile + " -> " + newFile
    Next n
End Sub

Using your examples, this works:

ABC.example.txt.****.txt -> example.txt
APX.example_********_********_******.txt.****.txt -> example_********_********_******.txt
XTR.BC_*******_example.****.txt -> BC_*******_example

 

[kennypete]

Hi @Kyletok - did the code above solve it? If yes, please mark as solved. Thanks