need help so that the results can be as I want

[roykana]

Dear All master,
if i use the code below after select range in column a then the results appear in column b and i want after column b the desired results appear in column c. Please solve

thanks
roykana

Sub GetFileNamebykana()

Dim title As String
Dim Rng As Range
Dim selection As Range
Dim splitpath As Variant

On Error Resume Next
title = "VBA by kana"


Set selection = Application.selection
Set selection = Application.InputBox("Range", title, selection.Address, Type:=8)
For Each Rng In selection
    splitpath = VBA.Split(Rng.Value, "\")
    Rng.Offset(0, 1).Value = splitpath(UBound(splitpath, 1))

Next
End Sub

 

[Joe4]

I think you just need to change this:

Rng.Offset(0, 1)

to this:

Rng.Offset(0, 2)

 

[roykana]

I think you just need to change this:

Rng.Offset(0, 1)

to this:

Rng.Offset(0, 2)

dear mr. joe4,
thanks for your reply. I tried the code from you, after selecting the range column a, the results appear in column b & c but the results in column c are not what I want, you can see the screenshot that I marked in yellow below.
thanks
roykana

 

[Joe4]

I cannot do anything with am image of the data like that. Can you please use the "XL2BB" tool to post your data? That lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[roykana]

dear mr. joe,

VBA-Extract Filename From Full Path.xlsm
	A	B	C
1	PATH	FILE NAME WITH EXT	DESIRED RESULT
2	C:\Users\ADMIN2\Documents\foto 03-08-2021\891170(1).jpg	891170(1).jpg	891170
3	C:\Users\ADMIN2\Documents\foto 03-08-2021\89109.jpg	89109.jpg	89109
4	\\server-pc\Katalog\KATALOG MALFIN 2015\AEP\56008(1)-A.jpg	56008(1)-A.jpg	56008
5	\\server-pc\Katalog\KATALOG MALFIN 2015\AEP\56127-A.jpg	56127-A.jpg	56127
6	\\server-pc\Katalog\KATALOG_TravelPlus\P013M..jpg	P013M..jpg	P013M.
7	\\server-pc\Katalog\KATALOG_KaryaAsiaJaya\K9569L..jpg	K9569L..jpg	K9569L.
8	\\server-pc\Katalog\KATALOG_KaryaAsiaJaya\2019 ..jpg	2019 ..jpg	2019 .
Sheet2

thanks
roykana

 

[Joe4]

OK, I misunderstood your question initially.
So what you are saying is that what you are showing in column B is what you are currently getting, but you want to actually get what you show in column C. Is that correct?
If so, I think further explanation is required.

It initially looks like you just want to remove the ".jpg" off of the end of each entry, but then it appears that you also sometimes want to remove things like "(1)" and "-A".
So I thought maybe you wanted to remove everything up to the first text letter it finds, but then that would not work for your last three entries.

So I am not seeing the pattern in determining which characters you want to show and which you do not.
Can you please list out all the rules (in plain English) we need to follow in order to determine what it is that you want returned?

 

[roykana]

OK, I misunderstood your question initially.
So what you are saying is that what you are showing in column B is what you are currently getting, but you want to actually get what you show in column C. Is that correct?

Yes that's correct

It initially looks like you just want to remove the ".jpg" off of the end of each entry, but then it appears that you also sometimes want to remove things like "(1)" and "-A".
So I thought maybe you wanted to remove everything up to the first text letter it finds, but then that would not work for your last three entries.

I want to remove something like (.jpg,(1),(2),(3),(1)-A,-A,-1) and if there is an addition that I want to remove apart from mentioning please can you comment on the code so I can add it alone.
do you understand from my answer?
Thanks

 

[Joe4]

OK, here is just a "guess" as how you want it to work. It works on all your sample data, but I don't know if I have interpretted all your conditions correctly (or if you provided a complete list):

Sub GetFileName2()

    Dim rng As Range
    Dim temp As String
    Dim arr1() As String
    Dim arr2() As String
    Dim arr3() As String

    For Each rng In selection
        temp = Replace(rng.Value, ".jpg", "")
        arr1 = Split(temp, "\")
        arr2 = Split(arr1(UBound(arr1, 1)), "(")
        arr3 = Split(arr2(0), "-")
        rng.Offset(0, 1).Value = arr3(0)
    Next rng

End Sub

 

[roykana]

OK, here is just a "guess" as how you want it to work. It works on all your sample data, but I don't know if I have interpretted all your conditions correctly (or if you provided a complete list):

Sub GetFileName2()

    Dim rng As Range
    Dim temp As String
    Dim arr1() As String
    Dim arr2() As String
    Dim arr3() As String

    For Each rng In selection
        temp = Replace(rng.Value, ".jpg", "")
        arr1 = Split(temp, "\")
        arr2 = Split(arr1(UBound(arr1, 1)), "(")
        arr3 = Split(arr2(0), "-")
        rng.Offset(0, 1).Value = arr3(0)
    Next rng

End Sub

thanks for your reply. I tried your code and it worked and I tried modifying it with the input box with range but I want the file name to appear in the column B with the extension

Sub GetFileName2()
    Dim title As String
    Dim rng As Range
    Dim temp As String
    Dim arr1() As String
    Dim arr2() As String
    Dim arr3() As String
On Error Resume Next
title = "VBA by kana"


Set selection = Application.selection
Set selection = Application.InputBox("Range", title, selection.Address, Type:=8)
    For Each rng In selection
        temp = Replace(rng.Value, ".jpg", "")
        arr1 = Split(temp, "\")
        arr2 = Split(arr1(UBound(arr1, 1)), "(")
        arr3 = Split(arr2(0), "-")
        rng.Offset(0, 2).Value = arr3(0)
    Next rng

End Sub

 

[roykana]

thanks for your reply. I tried your code and it worked and I tried modifying it with the input box with range but I want the file name to appear in the column B with the extension

Sub GetFileName2()
    Dim title As String
    Dim rng As Range
    Dim temp As String
    Dim arr1() As String
    Dim arr2() As String
    Dim arr3() As String
On Error Resume Next
title = "VBA by kana"


Set selection = Application.selection
Set selection = Application.InputBox("Range", title, selection.Address, Type:=8)
    For Each rng In selection
        temp = Replace(rng.Value, ".jpg", "")
        arr1 = Split(temp, "\")
        arr2 = Split(arr1(UBound(arr1, 1)), "(")
        arr3 = Split(arr2(0), "-")
        rng.Offset(0, 2).Value = arr3(0)
    Next rng

End Sub

and I have moved the desired results in column c