Jump to content

Seperating filename into coumns in Excel


goku200
 Share

Recommended Posts

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 by goku200
Link to comment
Share on other sites

_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.

Link to comment
Share on other sites

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 by goku200
Link to comment
Share on other sites

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

 

 

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...