Regex - Digital magic for process optimisation

Let's say you run an electronics webshop and you've just received a shipment of the latest 2019 models. The product information has been sent in an excel sheet, which you usually upload to the website to load the products.

Unfortunately, this vendor never includes their brand name in the title, and the inches of the TV are also in a column by themselves.

You want your future title to change from:
"55PUS7503/12 Ultra-thin 4K TV" to "Philips 55" Ultra-thin 4K TV"

Do you have to manually correct 100 models in excel before uploading the products?

The answer is NO - at least not with a little regex magic.

Common to all products is that their MPN number (the long number in front) always ends with a "/" followed by 2 digits. What we are interested in is the information that comes after these numbers, namely that the product is an "Ultra-thin 4K TV".

The MPN number will be unique for each product, so "Find & Replace" will not work. They also come in different lengths, so "=right" is not your saviour either.

Fortunately, excel has a regex function, "=Regexextract"

Koden (A2;“\/\d{2}\s(.*)”) fortæller systemet, at hvad end der kommer efter skråstregen og de to cifre, er hvad du gerne vil have, at titlen bliver.

Use this formula down through the sheet for the products and voila, you've just updated 100 products faster than you could brew a cup of coffee. As illustrated in the picture, field A3 is marked where the result of the formula is displayed.

All you need now is to use =SUMMARY(B2;" ";C2;" Inches ";A3) to include the data from the other rows, and you have the perfect product description.

You run a blog and have decided to get rid of the date tag in the URL so that your blog is no longer called:

"supersjovblog.dk/2019/08/20/Lentils-are-the-new-carbohydrates"
but is called: "supersjovblog.dk/blog/Lentils-are-the-new-carbohydrates"

How do you update your entire back catalogue of blog posts, without creating a redirect for each post? By using Regex of course!

Now we need to find a pattern to work with again. Common to all blog posts is that they have a date in front of them, which changes depending on when the post was posted.

This date "just" needs to be changed to /blog/ instead, as the end remains the same!

Med et stykke kode, og et redirect plugin som kan klare regex-udtryk, kan vi klare hele redirecten af bloggen med denne kode: \/\d{4}\/\d{2}\/\d{2}\/(.*) som skal redirecte til /blog/$1 og tada! 

All pages with the mentioned blog structure are now redirected to their new /blog/ URL! Completely worry-free!

Here you see the code working on a block example with the before example at the top and the changed result at the bottom.

The blue part is what matches the code, the green part is what we want to save.

You can also use this for regular URL changes, as long as the end is the same. Take a dentist's website as an example:

/dental-treatments/ to /treatments/ would be: which should be redirected to: \/treatments\/$1

But again, this only works if the end of the URL in both places is the same!

Your new webshop design is going live, and that means all your products will not only be in a new place, but their endings will change from "/" to ".html"

So you now go from:
denvildeplakatshop.dk/product/zebra-plakat/
to:
denvildeplakatshop.dk/shop/zebra-plakat.html

Without Regex, you'd have to manually make redirects until you ran out of hair to pull. But once again, Regex comes to the rescue.

\/product\/(.*)\/$ captures the product name within its slash.

/shop/$1\.html changes "product" to "shop", inserts the product title and adds ".html" at the end.

Plot the code in the same redirect plugin that can take Regex, and put a check mark next to the "Regex" box. You are now up and running.

With a single line of code you have just moved your entire webshop and saved yourself and your employees a lot of overtime and boring rye bread work!

They say a magician never reveals his tricks... but I've done that now - so you can be a magician at your workplace too, and save people time and money!

Throughout this blog I have used terms like:

\/
\d{2} og \d{4}
(.*)
$1
\s

... But what do these terms mean and what do they do?

In short, these terms tell the system what you're looking for. The first term related to the products was as follows:

\/\d{2}\s(.*)

Broken down it looks like this:

\/ just means /

\d{2} er egentlig to-delt information. “\d” betyder, at der skal være et tal og “{2}” står for antallet, i dette tilfælde 2, så her siger vi, at der skal være to tal.

\s stands for "space"

(.*) this is also two-part information. The period itself means "Any character".

The following character determines how many characters we are looking for, in this case it is "*" which stands for between 0 and unlimited. So ".*" is all characters. The brackets around ".*" just tell systems that what's inside them we want to save for later.

The way we then get it is with the dollar sign and what order the brackets are in. $1 is the first in order.

Når alle disse så er brudt ned og oversat, så fortæller koden (\/\d{2}\s(.*)), at
hvad end der kommer efter en skråstreg, efterfulgt af 2 tal og et mellemrum, det er vi interesseret i at gemme til senere.

Now that you've been spellbound and want to learn more than my brief disclosure, visit this page: https://regex101.com/.
Here you will find all the variables and you have a safe place to test your magic!

If magic tricks don't interest you, but you want to save yourself and your colleagues time and money, feel free to contact the professional magicians at webamp.dk on tel. 70 60 50 28 or info@webamp.dk