Seperating filename into coumns in Excel

[goku200]

I have an Autoit script that lists files from a folder into an array list. Is there a way to separate the filenames by an underscore and include the id, version, name and date into separate columns in Excel.

Example of filename:

12345_v1.0_TEST Name [12345]_01.01.2022.html

12345 would be in one column

v1.0 would be in another column

TEST Name [12345] would be in another column

01.01.2022 would be in another column

.html would be in another column

Note: filenames always change each day.

Here is my code that lists the files into column C and then writes the column Headers into Column D, E, F, G. Just need some help with separating them into columns by the _ delimiter

 

Edited February 19, 2022 by goku200

[Danp2]

I would split them first and then load them into Excel. Take a look at StringSplit and _ArrayAdd.

[Nine]

And to get the format of the file (ie. extension) use this RegExp pattern :  "(?:.*\.)(.*)"

[goku200]

@Nine I added the following and its still not splitting. Its adding the columns as expected and the filenames in column C but not splitting it.

 

Edited February 19, 2022 by goku200

[Nine]

_FileListToArray returns an array. StringSplit is by default 1-based, and your are not using StringRexExp for the extension. Please take a moment to understand what you are doing.  Put some error handling in your code to trace why some statements are faulted.

[goku200]

On 2/4/2022 at 5:44 PM, Nine said:

_FileListToArray returns an array. StringSplit is by default 1-based, and your are not using StringRexExp for the extension. Please take a moment to understand what you are doing.  Put some error handling in your code to trace why some statements are faulted.

Thanks for the reply @Nine. I went ahead and went a different route with this. I created a vba macro in Excel that separates my filenames into columns and then used Autoit to do the rest:

Sub Test_Formula()
    Dim i, LastRow As Long
   
    'Get last populated row in column one
    LastRow = Cells(Rows.Count, 1).End(xlUp).row
   
    'Loop thru rows
    For i = 2 To LastRow
        Range("B2:F" & LastRow).Formula = "=IF(LEN($A2)-LEN(SUBSTITUTE($A2,""_"",""""))+1>4,TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"".""&TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE($A2,""."","" "",LEN($A2)-LEN(SUBSTITUTE($A2,""."",""""))),"" "",REPT("" "",200)),100)),""_""&"".""&TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE($A2,""."","" "",LEN($A2)-LEN(SUBSTITUTE($A2,""."",""""))),"" "", REPT("" "",200)),100))),""_"","" "",3),""_"",REPT("" "",100)),COLUMN(B2)*99-98,100)), TRIM(MID(SUBSTITUTE(SUBSTITUTE($A2,"".""&TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE($A2,""."","" "",LEN($A2)-LEN(SUBSTITUTE($A2,""."",""""))),"" "",REPT("" "",200)),100)),""_""&"".""&TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE($A2,""."","" "",LEN($A2)-LEN(SUBSTITUTE($A2,""."",""""))),"" "",REPT("" "",200)),100))),""_"",REPT("" "",100)),COLUMN(B2)*99-98,100)))"
    Next i
   
End Sub

 

Edited February 8, 2022 by goku200

[junkew]

You can replace your formula by making a vba function and set that function making it a little more readable

n=0 for whole string last dot replaced with a _
n=1 for first word  and so on for getting your column

formula in cell is then like
B1=splitword(A1;1)

' splitword("12345_v1.0_TEST Name [12345]_01.01.2022.html",0)
' splitword("12345_v1.0_TEST Name [12345]_01.01.2022.html",1)
' splitword("12345_v1.0_TEST Name [12345]_01.01.2022.html",2)
' splitword("12345_v1.0_TEST Name [12345]_01.01.2022.html",3)
' splitword("12345_v1.0_TEST Name [12345]_01.01.2022.html",4)
' splitword("12345_v1.0_TEST Name [12345]_01.01.2022.html",5)
' splitword("12345_v1.0_TEST Name [12345]_01.01.2022.html",6)

Public Function splitWord(str As Variant, n As Variant) As String
    dotPos = InStrRev(str, ".")
    newstr = Left(str, dotPos - 1) & "_" & Mid(str, dotPos + 1)
    
    If n = 0 Then
        splitWord = newstr
    Else
        splitWord = Split(newstr, "_")(n - 1)
    End If
End Function

 

 

 

[goku200]

3 hours ago, junkew said:

You can replace your formula by making a vba function and set that function making it a little more readable

n=0 for whole string last dot replaced with a _
n=1 for first word  and so on for getting your column

formula in cell is then like
B1=splitword(A1;1)

' splitword("12345_v1.0_TEST Name [12345]_01.01.2022.html",0)
' splitword("12345_v1.0_TEST Name [12345]_01.01.2022.html",1)
' splitword("12345_v1.0_TEST Name [12345]_01.01.2022.html",2)
' splitword("12345_v1.0_TEST Name [12345]_01.01.2022.html",3)
' splitword("12345_v1.0_TEST Name [12345]_01.01.2022.html",4)
' splitword("12345_v1.0_TEST Name [12345]_01.01.2022.html",5)
' splitword("12345_v1.0_TEST Name [12345]_01.01.2022.html",6)

Public Function splitWord(str As Variant, n As Variant) As String
    dotPos = InStrRev(str, ".")
    newstr = Left(str, dotPos - 1) & "_" & Mid(str, dotPos + 1)
    
    If n = 0 Then
        splitWord = newstr
    Else
        splitWord = Split(newstr, "_")(n - 1)
    End If
End Function

 

 

 

Thank you @junkew I'm a little confused with this function. Its not displaying in the macro when I click on View > Macros in Excel. Am I missing something?

[junkew]

You should copy the vba function code to a module.

I am not sure but it could be only sub procedures are shown in macro overview.

When you are in vba editor you can enter debug.print splitword to test the function or in you spreadsheet use it as a formule.

[goku200]

On 2/8/2022 at 2:30 PM, junkew said:

You should copy the vba function code to a module.

I am not sure but it could be only sub procedures are shown in macro overview.

When you are in vba editor you can enter debug.print splitword to test the function or in you spreadsheet use it as a formule.

Got it to work finally. Thank you.