Thread: Parse file name and put parsed value into file

I have to open several files and then combine them together. What i am working on right now is when i open the file i have to parse the file name as it contains information that is specific to that file only. Also, its what i will use to differentiate the files once i put them all together.

I am enclosing an example of the file.

The file names are similar to the following:

Dav 101-20070611_16Jun2011.xlsx


What i would like is to parse this "Dav 101-20070611" and then store this into the file into a single column filling it down for as many rows are in the file. Not sure how to "efficiently" do this.

The text before the "_date" can be just about any combination of characters.

HI,


Here is some code that you may be able to use.
It goes down column A of sheet1 and then splits out the text and sends it to column A of sheet2.

Sub test()
Dim cel As Range
Dim lrow As Long
Dim MyTxt As String
Dim MyNewtxt As String
For Each cel In Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").Range("A65536").End(xlUp).Row)
        MyTxt = Sheets("Sheet1").Cells(cel.Row, 1).Text
        MyNewtxt = Split(MyTxt, "_")(0)
        lrow = Sheets("Sheet2").Range("A65536").End(xlUp).Row + 1
        Sheets("Sheet2").Range("A" & lrow).Value = MyNewtxt
Next
End Sub