How to use the filename of an excel file to change

Question

I am trying to write an excel macro/VBA code to adjust a column's contents based off the title of the file. Currently, I have a macro setup to download a csv file based off a URL I type in. The macro automatically formats and names the file. I want to use the first 5 characters of the file's name/title to automatically format additional cells.

For example, the filename is 48GHY_fjfdkjfe33.RHRH-msadklfjeiojo. I have a column in my file called name+type. I want to combine the "name" row with the title and have it populate the fields. The two images below provide a before and after.

Currently, I have to type in the first 5 characters into a seperate field and then use the: ="48GHY"&a2 function.

Is there a way to do this in excel/VBA?

---

The original question was answered, but now I am seeking additional help when trying to put it in a macro. Because it is still the same question, I do not think it warrants another post.

Here is the current macro/VBA I am using. When ever I run this, it just displays #VALUE! in the cell. The only thing different between the two sets of code is that the A and B columns are now J and K.

' Insert Column after name and then rename it name+type

Rows(1).Find("name").Offset(0, 1).EntireColumn.Insert
Rows(1).Find("name").Offset(0, 1).FormulaR1C1 = "name+type"

' Freeze the Top Row

Rows("1:1").Select
With ActiveWindow
    .SplitColumn = 0
    .SplitRow = 1
End With
ActiveWindow.FreezePanes = True

'Save the file

ActiveWorkbook.Save

'Add the contents to the name+type column

Range("K2").Select
ActiveCell.FormulaR1C1 = "=LEFT(MID(CELL(""filename"",RC[-1]),SEARCH(""["",CELL(""filename"",RC[-1]))+1,SEARCH(""]"",CELL(""filename"",RC[-1]))-SEARCH(""["",CELL(""filename"",RC[-1]))-1),5)&RC[-1]"
Range("K2").Select
Selection.Copy
Range("K2:K8294").Select
ActiveSheet.Paste

' Autofit all Columns

Columns("A:AK").Select
Selection.Columns.AutoFit

End Sub

*One additional comment. I modified the code you provided in the macro, but in each cell after I run it the following code is in column k:

=LEFT(MID(CELL("filename",J2),SEARCH("[",CELL("filename",J2))+1,SEARCH("]",CELL("filename",J2))-SEARCH("[",CELL("filename",J2))-1),5)&J2

*Last note. The file name is being saved as 48GHY_fjfdkjfe33.RHRH-msadklfjeiojo#csv.txt (Could that have anything to do with it?)

Answer 1

Put this in B2

=LEFT(MID(CELL("filename",A2),SEARCH("[",CELL("filename",A2))+1, SEARCH("]",CELL("filename",A2))-SEARCH("[",CELL("filename",A2))-1),5)&A2

EDIT

Explanation

This formula =CELL("filename",A2) gives you the file name. For example

C:\Users\Siddharth Rout\Desktop\[Book1.xlsx]Sheet1

and this formula

=MID(CELL("filename",A2),SEARCH("[",CELL("filename",A2))+1, SEARCH("]",CELL("filename",A2))-SEARCH("[",CELL("filename",A2))-1)

takes out just the file name from above to give you Book1.xlsx

The Left() returns the first character or characters in a text string, based on the number of characters you specify.

HTH

Answer 2

Maybe you don't even need VBA. Something like this worked for me.

=MID(CELL("filename",A2),FIND("[",CELL("filename",A2))+1,5) & A2

Lookup Cell formula to understand how filename works.