Extract Text from End of Line back to Character

[bokenrosie]

Hello Mr. Excel,

I'm trying to extract everything from a character "\" to end of the line that it's in. My challenge is some of the lines have one "\" character and others have many.

I just need what appears after the last "\" to the end of the line.

Can anyone tell me what function I need to do?

 

[pbornemeier]

x = MID(YOURSTUFF,INSTRREV(YOURRSTUFF,"\"))

 

[CA_Punit]

Try this

Book1
	F	G	H	I
8	gaga/afsyaj/atatav/ja			ja
9
10
11
Sheet5

Cell Formulas
Range	Formula
I8	I8	=MID(F8,SEARCH("||",SUBSTITUTE(F8,"/","||",LEN(F8)-LEN(SUBSTITUTE(F8,"/",""))))+1,999)

 

[bokenrosie]

Is Yourstuff supposed to be the column with the string? column A has my list in it. replacing Yourstuff with A2 did not seem to work.

 

[bokenrosie]

Try this

Book1
	F	G	H	I
8	gaga/afsyaj/atatav/ja			ja
9
10
11
Sheet5

Cell Formulas
Range	Formula
I8	I8	=MID(F8,SEARCH("||",SUBSTITUTE(F8,"/","||",LEN(F8)-LEN(SUBSTITUTE(F8,"/",""))))+1,999)

That looks like exactly what I want. I tried replacing F8 with A2 and it did not work.

 

[Fluff]

Another option
=TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",100)),100))

 

[bokenrosie]

Maybe I did'nt explain well. Please look at the follwoing, on the left column is what I have and on the Filename is the result I'm looking for. I've tried to the two methods suggested and they are not giving me this result. thank for trying.

Location	Filename
c:\users\joe\projects\smith\Excel Examples.jpg	Excel Examples.jpg
c:\users\tom\projects\demos\2002\My First File.doc	My First File.doc
c:\users\mary\Green Leaves.mpg	Green Leaves.mpg

 

[CA_Punit]

=MID(F8,SEARCH("||",SUBSTITUTE(F8,"\","||",LEN(F8)-LEN(SUBSTITUTE(F8,"\",""))))+1,999)

Syntax Error

 

[ER_Neha]

Hello, I hope this helps

Book2
	A	B
1	c:\users\joe\projects\smith\Excel Examples.jpg	Excel Examples.jpg
2	c:\users\tom\projects\demos\2002\My First File.doc	My First File.doc
3	c:\users\mary\Green Leaves.mpg	Green Leaves.mpg
4
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",LEN(A1))),LEN(A1)))

 

[sandy666]

with Power Query

Location	Text After Delimiter
c:\users\joe\projects\smith\Excel Examples.jpg	Excel Examples.jpg
c:\users\tom\projects\demos\2002\My First File.doc	My First File.doc
c:\users\mary\Green Leaves.mpg	Green Leaves.mpg

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TAD = Table.AddColumn(Source, "Text After Delimiter", each Text.AfterDelimiter([Location], "\", {0, RelativePosition.FromEnd}), type text)
in
    TAD