[Solved] Wildcards question: Trimming URLs

[GavinAD]

Hi all,

Long-time lurker here.

I have a document containing 20,000 URLs. They go to specific webpages. I need to cut the URL so that it goes to the page before.

This post before was the closest I could find to my answer for a problem I have: viewtopic.php?f=9&t=58098. However, I need the inverse of the solution provided.

So, for example, in this above URL (viewtopic.php?f=9&t=58098), I need a wild card that cuts out "viewtopic.php?f=9&t=58098" but leaves the remainder (https://forum.openoffice.org/en/forum/)

In the linked post @Acknak offers the solution for the opposite (^.*[/\\]). That works perfectly but it does the opposite of what I need. I've spent a couple of hours fiddling and reading up and still can't work out how to do the inverse.

Would really appreciate any help from the wildcard whizzes out there — and, should you see this @Acknak, thanks a lot for helping the other person; yours gave me hope that it was possible...!

Thanks a lot,

Gavin

[FJCC]

Try this as your search term.

Code: Select all

[^/\\]+$

It is looking for characters that are not / or \ up to the end of the paragraph.

[GavinAD]

Lifesaver. Thanks so, so much. I won't pretend to understand what you've written or why it works but appreciate that it does!

Two other things, if anyone knows this: I want to now search for the part at the end of the paragraph between the two slashes and including the final slash.

So for the example above: https://forum.openoffice.org/en/forum/ the search should find only "forum/" - this'll enable me to then go back through the data and work out whether these need to be slimmed back and simply replace them if they're redundant.

Finally, how do find and replace cells that are duplicates within a column? For instance, here, I'd end up with lots of instances of "https://forum.openoffice.org/en/forum/" where I just want one remaining. Thanks!

[GavinAD]

Managed to solve the first question below, I think:

([^/]+$)

How am I able to remove duplicate entries in the same column please, anyone?

[FJCC]

Say your URLs are in A1:A1000. In B1 put the formula

Code: Select all

=COUNTIF(A$1:A1;A1)

and copy it down the column. That will return 1 the first time an entry appears and it will return 2 or more on subsequent entries. You can then filter column B for values of 1 and use Copy and Paste to place those entries elsewhere or you can filter for values greater than 1 and delete those rows. I avoid deleting data, so I would use the first option.

[Villeroy]

How am I able to remove duplicate entries in the same column please, anyone?

Select the column
menu:Data>Filter>Standard Filter...
Field name: column x
Condition: =
Value: -- not empty -- (selectable from the list)
[More Options]
[X] No duplicates