How to Remove Tracking and Special Characters in Excel - TM Blast

How to Remove Tracking URL’s in Excel

Tracking URL’s are special characters added to paid search campaigns, social media campaigns, email campaigns, and pretty much anything that you want to specifically track for marketing purposes. While great for separating out channels by the source, you will quickly realize the nightmare it creates when you want to check your data. In this blog post, I am going to show how to update your excel formula to remove everything after a special character like a question mark. You want to do this so you are presented with a clean URL (without the tracking), so you can put everything into a Pivot Table and review the data. Here are the steps below.

 

Excel Steps to Remove Text After a Specific Character

Here is an example of exporting the top landing pages with the sessions that they drove from Google Analytics. I can see clean URL’s and I can see marketing URL’s that have a question mark associated to them. If I went through this manually, I would spend a lot of time removing parts of the URL to make it clean. Since we want to clean up all of this data before we put it into a pivot table, we will need to use the FIND function in Excel.

Tracking URL's Cause Problems in Excel

 

Step 1: Create a New Column To Start the Function

The new column needs to go to the right. 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 be created to run the rule to remove everything after a certain character.

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 lets’ Excel know that this is where Excel needs to look to FIND a special character. As a note, you don’t want any special characters on this section because you don’t want to lock any part of this function.

Step 3: Specify The Special Character

After looking to the left and looking for a special character, you then tell Excel what to exactly look for when they arrive to column A. Since we are looking for marketing tracking in the URL, I know that this is created dynamically after a question mark. You can visualize see that with this list below. So the formula now goes look to the left, looks in column A, and tries and find a question mark. From there, the formula reads to then look one over to the right of the question mark and then only keep one left of the question mark. You will see this in the example at the bottom where the formula ran correctly and kept a clean URL.

How to Remove Tracking URL's in Excel

Step 4: Sort

After the formula has been dropped down to all sections of column A, we need to sort the values. Sorting the values will allow us to then manually move the clean part of the URL (under column called fix) to the landing page column in column A.

Sort Removed Tracking URL's

Step 5: Replace all URL’s with the Clean Version

After we’ve sorted all the data in the fixed column, we are going to copy all the changed URL’s and paste them on the left column. This will turn column C into a #VALUE! tag since the everything has been fixed. The formula is still running, so you can see that there are no more tracking URL’s in column A and everything is ready to go.

Successful Removal of Tracking URL's in Excel

Step 6: Put Everything into a Pivot Table

With all the URL’s in a clean version, we need to put everything into a pivot table. You will see that many pages are now separated by a row. The pivot table will combine all of these duplicates and add up the sessions for them. Here is that view below that sorts the sessions from highest to lowest.

 

Put Everything into a Pivot Table to Get a True Session Measurment

 

Conclusion

If you have more than just one type of common tread (like if you have internal search pages that are indexed), you will need to repeat these steps but change the rule to include the common thread. This command in excel looks for the character that you tell it to and removes everything after that. If you wanted to do the reverse and actually remove everything before the question mark, you will need to use the RIGHT command in excel. To do this, you will basically follow the same steps, but you will need to update the formula to include the RIGHT function. This is the snapshot below on how this will look.

 

RIGHT Command in Excel

1686 Massachusetts Avenue Apartment C, Cambridge Massachusetts 02138
877-425-2141