Inserting a filename in a cell and resizing

[chadski778]

I would like to insert a filename into a cell (A6). Unfortunately, the filename contains a string that I do not require. It consists of a product name (required), space, formulation code, space, date. Could I have code for inserting a filename in A6 and then removing the last 20 characters, including spaces, from the right side of the filename. A typical filename would be 'shell helix 140001983331 10Aug11'. I just want 'shell helix'

Thanks

 

[VoG]

Try like this

Range("A6").Value = Left(fName, Len(fName - 20))

 

[GUINNESS85]

This may work

J40 =CELL("FILENAME")
=REPLACE(J40,1,89,"")

just use different range numbers

 

[chadski778]

Range("A6").Value = Left(fName, Len(fName - 20))

This isn't working for some reason!

 

[VoG]

Oops!

Range("A6").Value = Left(fName, Len(fName) - 20)

 

[chadski778]

It's still not working???

 

[VoG]

Working here

Sub test()
Dim fName As String
fName = "shell helix 140001983331 10Aug11"
Range("A6").Value = Left(fName, Len(fName) - 20)
End Sub

 

[chadski778]

The filename i gave was just an example. I want it to work automatically with different filenames that are in a similar format to the one i provided

 

[chadski778]

It works if I use

Range("A6").Value = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 20)

Thanks for your help