title: How to use the filename of an excel file to change a column of cells?
body: <p>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.</p>
  
  <p>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.</p>
  
  <p><img src="https://i.stack.imgur.com/fbcb7.png" alt="Before Database"></p>
  
  <p><img src="https://i.stack.imgur.com/vcazs.png" alt="After Database"></p>
  
  <p>Currently, I have to type in the first 5 characters into a seperate field and then use the:
  ="48GHY"&amp;a2 function.</p>
  
  <p>Is there a way to do this in excel/VBA?</p>
  
  <hr>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>' 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"
  </code></pre>
  
  <p>' Freeze the Top Row</p>
  
  <pre><code>Rows("1:1").Select
  With ActiveWindow
      .SplitColumn = 0
      .SplitRow = 1
  End With
  ActiveWindow.FreezePanes = True
  </code></pre>
  
  <p>'Save the file</p>
  
  <pre><code>ActiveWorkbook.Save
  </code></pre>
  
  <p>'Add the contents to the name+type column</p>
  
  <pre><code>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)&amp;RC[-1]"
  Range("K2").Select
  Selection.Copy
  Range("K2:K8294").Select
  ActiveSheet.Paste
  </code></pre>
  
  <p>' Autofit all Columns</p>
  
  <pre><code>Columns("A:AK").Select
  Selection.Columns.AutoFit
  </code></pre>
  
  <p>End Sub</p>
  
  <p>*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:</p>
  
  <pre><code>=LEFT(MID(CELL("filename",J2),SEARCH("[",CELL("filename",J2))+1,SEARCH("]",CELL("filename",J2))-SEARCH("[",CELL("filename",J2))-1),5)&amp;J2
  </code></pre>
  
  <p>*Last note. The file name is being saved as 48GHY_fjfdkjfe33.RHRH-msadklfjeiojo#csv.txt (Could that have anything to do with it?)</p>