Tracking URL’s are special characters that are extra additions to static URL’s. Examples of tracking URL’s include paid search campaigns, social media campaigns, email campaigns, and anything else you want to track specifically in a tool like Google Analytics. Marketing URL’s are fantastic for separating out channels from the source, but you will realize the nightmare it creates when you want to check your data as a whole. In this blog post, I will demonstrate show how to create an Excel formula that removes everything from a unique character. By finding the special character in excel like a question mark, you can strip away all of the extra URL additions that come from marketing URL’s.
Excel Steps to Remove Text After a Specific Character
Here is a top landing page report from Google Analytics that shows me both static and dynamic URL’s. If my task was to review how traffic as a whole we drove to each page, I would spend a lot of time trying to manually sort these URL’s within Excel. Since we are using Excel, we are going to use the FIND function to run custom rules to strip away the marketing URL’s.
Step 1: Create a New Column To Start the Function
Creating a new column to the right of whatever we want to work off is the way to begin a formula in Excel. You can call the new column whatever you want, but I call it “fix” for this example. This new column will be where our function will live and will be where the rule runs.
Step 2: Create the LEFT and FIND Formula
The LEFT command tells Excel that this rule needs to look to the left to begin the operation. The “A5” part of the formula informs Excel know that this is where it needs to look to FIND a unique character.
Step 3: Identify The Special Character
Since we are looking for marketing tracking in the URL, I know that this is in the form of a question mark. The formula tells Excel to look to the left, look at column A, and find question mark if one exists. You will see this in the example at the bottom where the formula ran correctly and kept a clean URL. Effectively, I tell Excel to look at the characters to the left of the question mark and to remove everything to the right if it has a question mark in the cell of reference.
Step 4: Sort
After the formula has run down column A, we need to sort the values. Sorting the values will allow me to manually move the clear part of the URL to the landing page column in column A.
Step 5: Replace all URL’s with the Clean Version of Itself
After sorting all the data in the fixed column, we copy all the updated URL’s and paste them in the left column. This will turn column C into a #VALUE! Tag since everything has been fixed. The formula is still running, so you can see that there are no more tracking URL’s in column A.
Step 6: Put Everything into a Pivot Table
With the URL’s in a clean version state, we need to put everything in a pivot table. You will see that a row now separates many pages. The pivot table will combine all of the duplicate URL’s and will sum up the sessions for them. Here is that view below that sorts the courses from highest to lowest.
Conclusion
If you want to remove everything before the question mark, you would need to use the RIGHT command. To do this, you update the formula to include the RIGHT function instead of LEFT. If you choose LEFT, you mean to keep everything to the left of the special character. If you choose RIGHT, you want to keep everything to the right of the special character.
This is the snapshot below of how this will look.
