find last / and extract the value

[nikhil0311]

Problem	Solution
sites/213870278/_catalogs/masterpage/display templates/filters	Filters
sites/213870278/_catalogs/masterpage/display templates/filters/control_refinement.js	control_refinement
sites/213870278/_catalogs/masterpage/display templates/filters/filter_default.js	filter_default

<colgroup><col><col></colgroup><tbody>
</tbody>

I have data in column A. and final output in Column B

I want to find the last "/" and exclude "values after full stop" in column A and have the solution in Column B.

Criteria "Find last "/" and exclude value after "fullstop"

<tbody>
</tbody>

 

[sandy666]

try PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type", "Text Between Delimiters", each Text.BetweenDelimiters([Column1], "/", ".", {0, RelativePosition.FromEnd}, 0), type text)
in
    #"Inserted Text Between Delimiters"[/SIZE]

Column1	Text Between Delimiters
sites/213870278/_catalogs/masterpage/display templates/filters	filters
sites/213870278/_catalogs/masterpage/display templates/filters/control_refinement.js	control_refinement
sites/213870278/_catalogs/masterpage/display templates/filters/filter_default.js	filter_default

 

[MickG]

This should do what you want !!
Results in column "B"

[COLOR="Navy"]Sub[/COLOR] MG24Aug49
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Sp = Split(Dn.Value, "/")
    Dn.Offset(, 1).Value = Split(Sp(UBound(Sp)), ".")(0)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mse330]

This is a UDF (User

Defined ​

Function) which should do the trick as well

Function LastValue(Txt As String) As String
LastValue = Split(Txt, "/")(UBound(Split(Txt, "/")))
If InStr(1, LastValue, ".") > 0 Then LastValue = Split(LastValue, ".")(0)
End Function

Book1
	A	B
1	Problem	Solution
2	sites/213870278/_catalogs/masterpage/display templates/filters	filters
3	sites/213870278/_catalogs/masterpage/display templates/filters/control_refinement.js	control_refinement
4	sites/213870278/_catalogs/masterpage/display templates/filters/filter_default.js	filter_default
Sheet1

Cell Formulas
Range	Formula
B2	=LastValue(A2)

<strike></strike>

 

[jtakw]

Hi,

Here's a formula solution:

Book1
	A	B
1	Problem	Results
2	sites/213870278/_catalogs/masterpage/display templates/filters	filters
3	sites/213870278/_catalogs/masterpage/display templates/filters/control_refinement.js	control_refinement
4	sites/213870278/_catalogs/masterpage/display templates/filters/filter_default.js	filter_default
Sheet206

Cell Formulas
Range	Formula
B2	=TRIM(LEFT(SUBSTITUTE(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",100)),100),".",REPT(" ",200)),200))

Formula copied down.

 

[Rick Rothstein]

@mse330 and jtakw,

Both of your solutions will fail if the filename contains a dot within it (for example, filters.one.js just to make up a filename).



@everyone,

Here is a UDF which works around the above possible problem...

[table="width: 500"]
[tr]
	[td]Function FileName(FullPath As String) As String
  FileName = Mid(FullPath, InStrRev(FullPath, "/") + 1)
  If InStr(FileName, ".") Then FileName = Left(FileName, InStrRev(FileName, ".") - 1)
End Function[/td]
[/tr]
[/table]

 

[jtakw]

Thanks Rick, but all OP said was

Criteria "Find last "/" and exclude value after "fullstop"

So when OP clarifies if there's that possibility as you mentioned, and he wants to exclude Only after the Last "fullstop", I'll try to adjust my formula to suit.

 

[Rick Rothstein]

Thanks Rick, but all OP said was

So when OP clarifies if there's that possibility as you mentioned, and he wants to exclude Only after the Last "fullstop", I'll try to adjust my formula to suit.

To be fair, I did refer to it as a "possible problem"... I just wanted the OP to be alerted just in case.

 

[nikhil0311]

Thanks All. Really appreciate your support and help . God bless us ALL

 

[MickG]

You're welcome