Extract Last Name After Mark "\" Without Extension in One Column

[muhammad susanto]

hi all..

i'm looking for a formula to figure out this problem. How to extract or get last name file without extension in single column, this just my sample

Book1
	B	C
2	data	expeted result
3	D:\test\xxxxx\abc\a\1wbbm\ND 131.pdf	ND 131
4	D:\test\zz\test\a\nd 346 himbauan.jpg	nd 346 himbauan
5	D:\test\Rekapitulasi.docx	Rekapitulasi
Sheet1

'i,m using Excel 2021 and please, don't use vba
anyone help, greatly appreciated..

susant

 

[JvdV]

This is ms365 (as per your profile):

Book1
	A	B
1	data	expeted result
2	D:\test\xxxxx\abc\a\1wbbm\ND 131.pdf	ND 131
3	D:\test\zz\test\a\nd 346 himbauan.jpg	nd 346 himbauan
4	D:\test\Rekapitulasi.docx	Rekapitulasi
Sheet2

Cell Formulas
Range	Formula
B2:B4	B2	=TEXTBEFORE(TEXTAFTER(A2:A4,"\",-1),".")
Dynamic array formulas.

And this should work on Excel 2021:

Book1
	A	B
1	data	expected result
2	D:\test\xxxxx\abc\a\1wbbm\ND 131.pdf	ND 131
3	D:\test\zz\test\a\nd 346 himbauan.jpg	nd 346 himbauan
4	D:\test\Rekapitulasi.docx	Rekapitulasi
5	D:\test\foo.tar.gz	foo
Sheet2

Cell Formulas
Range	Formula
B2:B5	B2	=FILTERXML("<t><s>"&SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(A2,"\","</s><s>")&"</s></t>","//s[last()]"),".","</s><s>")&"</s></t>","//s[1]")

 

[muhammad susanto]

hi JvdV, thank you , this is Amazing.

 

[Peter_SSs]

And this should work on Excel 2021:

Up to the OP of course, but if such data is possible, row 5 does not look correct to me.
Given the comment in post #3 I am assuming that the file name itself does not contain a "." character apart from before the file name extension.
If that is the case then this simpler one should also work in 2021

23 02 24.xlsm
	B	C
2	data	expeted result
3	D:\test\xxxxx\abc\a\1wbbm\ND 131.pdf	ND 131
4	D:\test\zz\test\a\nd 346 himbauan.jpg	nd 346 himbauan
5	D:\test\Rekapitulasi.docx	Rekapitulasi
File Name

Cell Formulas
Range	Formula
C3:C5	C3	=LET(t,TRIM(RIGHT(SUBSTITUTE(B3,"\",REPT(" ",99)),99)),LEFT(t,FIND(".",t)-1))

 

[muhammad susanto]

hi Peter, thank you.

 

[Peter_SSs]

hi Peter, thank you.

You're welcome.
So, what version are you actually using?

 

[JvdV]

@Peter_SSs

Yeah, it is just an example of file-extensions having dots. In this case a tar.gz file. Probably irrelevant.

 

[muhammad susanto]

You're welcome.
So, what version are you actually using?

for personal, i'm using 2021 but for in my department use 365

 

[Peter_SSs]

for personal, i'm using 2021 but for in my department use 365

Then what about for the question you are asking in this thread. What version(s) does the solution need to work in?

 

[muhammad susanto]

For this case, version 2021 that working well and that i need it.