CODEWITHSUNDEEP
excel-formula
Natan
Natan

Reputation: 71

How to extract the final slug of a url without trailing slash in excel?

I have a bunch of URLs:

https://www.example.com/XX/YY/cat/this-url-has-12345/
https://www.example.com/XX/YY/cat/this-url-has-different-length-67891/

I am trying to extract the slug (everything after /cat/) but without the ending trailing slash. Though, failing to get rid of the trailing slash.

I am using the below function but can't seem to negate an index to avoid the trailing slash:

RIGHT(C2;FIND("/cat/";C2;1))

All slugs would be in different length though the pattern before slug would be same.

Any functions to get only the slug without trailing slash? Thanks!

Upvotes: 0

Views: 1131

Answers (1)

General Grievance
General Grievance

Reputation: 4988

Use MID:

=MID(C2,FIND("/cat/",C2)+5,LEN(C2)-4-FIND("/cat/",C2)-(RIGHT(C2)="/"))

or in your locale:

=MID(C2;FIND("/cat/";C2)+5;LEN(C2)-4-FIND("/cat/";C2)-(RIGHT(C2)="/"))

  • MID arg 2: It finds the /cat/ and extracts the sequence from the string after that (+5).
  • MID arg 3: Extract length is the length of the rest of the string, minus 1 character depending on whether or not it ends with a slash.

Upvotes: 1

Related Questions