Formula Friday #18
Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.
Today we’re going to be looking at the SUBSTITUTE Function
This function can help you to make amendments to specific characters within your copy or URLs. It’s super quick and I’m sure as soon as you start using it you won’t turn back.
Let’s say you’re going to be setting up a new product category on site. And you have a like for like category, that you already know, has optimised URLs. Instead of typing out each new URL individually you can use the SUBSTITUTE Function to change only the characters, or words, which need changing.
A couple of things to note
The SUBSTITUTE Function is case sensitive. So if you were trying to amend the contents of your copy, you would need to ensure the text is formatted constantly across all the instances you want to change. Alternatively, if there is a specific instance of the text that needs changing you are able to do that.
Here we have changed the second instance of “dresses” to “dress” for all the URLs with the exception of knitted dresses, as it only has one instance of “dresses”.
As with all Functions, combining SUBSTITUTE with other Functions can create something quite special.
Like getting the page from the URL
Let’s say you have a long list of URLs that need checking, but first you want to be able to split them into groups. For example category pages and product pages. You could do this really manually and strain your eyes to carefully add in a key for yourself. Or you could use a formula that looks like this
=TRIM(RIGHT(SUBSTITUTE(A18,“/”,REPT(” “,100)),100))
And leave Excel to do the bulk of the heavy lifting. The formula is a bit of a head scratcher when you first look at it. So we’ll break it down and take a closer look at what each section is doing
Like getting the page from the URL
Let’s say you have a long list of URLs that need checking, but first you want to be able to split them into groups. For example category pages and product pages. You could do this really manually and strain your eyes to carefully add in a key for yourself. Or you could use a formula that looks like this
=TRIM(RIGHT(SUBSTITUTE(A18,“/”,REPT(” “,100)),100))
And leave Excel to do the bulk of the heavy lifting. The formula is a bit of a head scratcher when you first look at it. So we’ll break it down and take a closer look at what each section is doing
We’re going to look at this formula from the inside out
We’ve covered nesting formulas, so we know that each Function within a nested string is dependent on the outcome of another. In this case the very middle of the formula is the catalyst for the overall end result. So seems like a good place to start.
=SUBSTITUTE(A18,“/”,REPT(” “,100))
Here we are saying to Excel that we want to remove the forward slash within the URL and replace it with 100 spaces. The number of spaces is relative, you will need to ensure you are asking Excel to insert enough spaces so that it is at least 1 character more than your longest page name. We will get into why that is in a moment.
As you can see the URL in cell B23 has really stretched out, it’s no longer visible, in full, within the width of the column.
Now the different sections of the URL are separated out, we can make a selection only for the part we need to use.
By using the RIGHT Function we are telling excel to return the ‘X number of characters’ starting from the last character in the text string.
In this example our text string is the extended URL we just created, with all the spaces. Which means 100 characters from the right will surface the page of the URL, along with a whole bunch of spaces.
=RIGHT(SUBSTITUTE(A18,“/”,REPT(” “,100)),100)
We will take a short pause here
I want to make sure you’re still with me before we move on.
- First we replaced all the forward slashes with 100 spaces to stretch out the URL.
- Then we have extracted the last 100 characters of the stretched URL.
Because we used the RIGHT Function, the 100 characters is made up of actual letters, which will be the page name, and some spaces.
The final step is to clean up the text we have extracted, using the RIGHT Function. So that is only shows the URL page. To do this we will use the TRIM Function. The TRIM Function can remove any trailing and preceding spaces within text. While maintaining the spaces between text.
=TRIM(RIGHT(SUBSTITUTE(A18,“/”,REPT(” “,100)),100))
Final thoughts
The number of spaces you add in, instead of the forward slash, doesn’t have to be 100 but it also can’t be 1. You will need to add in the number of spaces that are at least 1 character more than your longest page name.
In the above image, you can see how the formula has been used to return the page names of two different lengths. This is possible as they are both shorter than 100 characters. There for meaning they will be preceded but spaces that can be trimmed off.
This is definitely a trial and error formula. So if you do get stuck, feel free to drop me a line.
Until next time,
Ax
Like this post? You should see my newsletter.
Subscribe to the Excel & ME newsletter to get updates that will empower you to inform your business decisions with data.
