Copy part of a Cells Text and paste using vba

[JEames]

Hi,

I know that this is probably really easy but I need help please.

Cell "B6" contains a folder location and file name as follows: \\abc002\Commercial\Live Workbooks\Test Workbook.xlsm
I want Cell A6 to contain the Workbook Name and File Extension only as follows: Test Workbook.xlsm

Any help appreciated.

 

[Fluff]

Try
=TRIM(RIGHT(SUBSTITUTE(B6,"",REPT(" ",100)),100))

 

[JEames]

Thanks but that didn't work it gives the the whole value in the cell.

 

[Fluff]

Not for me it didn't

Excel 2013/2016
	A	B
6	Test Workbook.xlsm	\\abc002\Commercial\Live Workbooks\Test Workbook.xlsm
Sheet1

Cell Formulas
Range	Formula
A6	=TRIM(RIGHT(SUBSTITUTE(B6,"",REPT(" ",100)),100))

What are your actual values?

 

[JEames]

They are exactly as yours are: Sorry don't know how to add a screen shot.

 

[Fluff]

In an unused cell type
=CODE(b6)
What dose it return?

 

[JEames]

92

 

[Fluff]

Ok, that's fine.
What does this return
=SUBSTITUTE(B6,"","")

 

[JEames]

Ok, that's fine.
What does this return
=SUBSTITUTE(B6,"","")

[TABLE="width: 404"]
<tbody>[TR]
[TD="class: xl64, width: 404"]\\abc002\Commercial\Live Workbooks\Test Workbook.xlsm[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Keep forgetting that the board software has a tendency to strip out \ characters when wrapped i quotes.

The formula should be
=TRIM(RIGHT(SUBSTITUTE(B6," \ ",REPT(" ",100)),100))
you need to remove the spaces around the \