CODEWITHSUNDEEP
excelexcel-formula
Unicorn
Unicorn

Reputation: 295

Get value after end of last / in excel using formula

i want to get the last value after / in excel.

www.google.com/new/oldpage.php
www.google.com/new/hello/wright.html
www.facebook.com/face/newface/demo.php

from the above link i want to separate the pagename for eg. from 1 url output should be www.google.com/new/ 2nd www.google.com/new/hello/ and so i used RIGHT formula but we can define only static. pls. provide if any one has the solution.

Upvotes: 1

Views: 163

Answers (3)

Tarod
Tarod

Reputation: 7170

If A1 is www.google.com/new/oldpage.php, use this code to get www.google.com/new/:

=LEFT( A1, FIND( "|", SUBSTITUTE( A1, "/", "|", LEN(A1)-LEN(SUBSTITUTE(A1,"/","")) ) ) )

Use this code to get oldpage.php:

=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1,"/","|",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

As you can see, you need to SUBSTITUTE some chars before calling the FIND method.

Depending on your regional options, you must change "," (comma) in the formula for ";" (semicolon).

Upvotes: 1

Steven Martin
Steven Martin

Reputation: 3272

Excel doesn't provide a formula for reverse string searches so the following must be used

=IF(ISERROR(FIND("/",A1)),A1,RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))))

Upvotes: 0

Davesexcel
Davesexcel

Reputation: 6984

Use this formula,

=MID(A16,SEARCH("^^",SUBSTITUTE(A16,"/","^^",LEN(A16)-LEN(SUBSTITUTE(A16,"/",""))))+1,999)

Upvotes: 0

Related Questions