CODEWITHSUNDEEP
excelexcel-formula
Martin
Martin

Reputation: 667

Excel function to remove parameters from URL

I have a cell in Excel filled with URL link, that goes

https://something.com/team?WT_from=TopMenu&WT_fname1=team

And I need to remove all parameters ?WT_from=TopMenu&WT_fname1=team so that the cell will look like this: https://something.com/team

Although all cells differs, hence there are no two same parameters. That means, i need a general formula removing all content behind the first quistionmark.

I tried to experiment with TRIM function, but none work. Any advice would be appreciated. Thanks.

Upvotes: 1

Views: 929

Answers (1)

A.Steer
A.Steer

Reputation: 411

If the text element at the end of the string is always the same, you can use the Find and Replace functions in excel. If your URL is in cell A1 the following should work

=REPLACE(A1,FIND("?WT_from=TopMenu&WT_fname1=team",A1),LEN(A1),"")

The find formula (FIND("?WT_from=TopMenu&WT_fname1=team",A1)), finds the relevent text in cell A1 and outputs a value that denotes at what character the string starts

The replace formula then looks in cell A1, finds where the text value starts and then uses the LEN function to find the total length of the text and then replaces this with blank. (The "" at the end of the formula)

This wont work if the text variable at the end is different each time or if there is other text at the end of the element that you want to remove, that you dont want to remove.

As per your edit, to remove all text after a ? you would need the following formula

=REPLACE(A1,FIND("?",A1),LEN(A1),"")

Upvotes: 1

Related Questions