Web scraping in Excel

Web scraping in Excel means you paste a list of page URLs into a column, and the add-in downloads every page and pulls out what you need: price, stock status, phone number, image link. No browser, no manual copy-paste and no programming.

If you only need page titles, H1 headings and meta descriptions, read the article on scraping Title, H1 and Description. This one is about arbitrary data from pages, extracted with regular expressions.

What you can scrape from websites into Excel

Typical tasks people scrape websites into a spreadsheet for:

• product prices and stock status — your competitors’ or your own;
• service prices: “from $45/hour”, “starting at $120”;
• contact details: phone numbers, emails, messenger links;
• product images: links to photos, and then the images themselves right in cells;
• assortment: how many product cards a category page has.

How regex scraping works in !SEMTools

The procedures are on the !SEMTools tab: Web (Scraping, SEO, PPC) → Extract → From URLs → Content by RegExp pattern. The menu has three commands:

• Case-sensitive — finds all matches of the regular expression in the page HTML;
• Case-insensitive — the same, but “Price” and “price” are treated as equal;
• Count occurences — returns the number of matches instead of the matches themselves.

The commands work in the full version of !SEMTools. The workflow is simple:

1. Paste full page URLs, with https://, into a column.
2. Copy this column next to it: the results replace the URLs, and you’ll still need the original list.
3. Select the cells with URLs, choose a command and enter a regular expression.
4. Wait for the result: the add-in downloads the pages one by one and writes what it found into each cell.

If a page has several matches, they go into one cell separated by three backslashes: “$129.99\\\$99.99”. To split them into columns, use the built-in Data → Text to Columns → Delimited → Other: \ with “Treat consecutive delimiters as one” checked, or the TEXTSPLIT function in Excel 365.

Three things to know in advance

• The procedure returns the whole match, not a capture group in parentheses. So build patterns that match only the data you need, and clean up the rest with the REGEXEXTRACT and REGEXREPLACE functions (more on them below).
• Regular expressions follow the VBScript engine rules: lookahead (?=…) works, lookbehind (?<=…) doesn’t. \s doesn’t include the non-breaking space, so specify it explicitly in prices: \u00a0 or &nbsp;.
• The add-in parses the page’s source HTML — what you see with Ctrl+U in a browser. If data is loaded by a script after the page loads, it isn’t in the source and the pattern finds nothing. Always check first that the data is in the source code.

Scraping product prices and stock status

A price in the page code usually looks like a dollar sign followed by a number with thousands separators and cents. This pattern takes it whole:

\$\s?\d{1,3}(?:,\d{3})*(?:\.\d{2})?

On a page with the price “$129.99” and the old price “$149.99” the result is “$129.99\\\$149.99”. To get a clean number, apply the REGEXREPLACE function to the cell: it removes everything except digits and the decimal point. If there are several prices in a cell, split them into columns first.

=REGEXREPLACE(B2,"[^0-9.]","")

It’s more reliable to take the price from Schema.org markup if the site has it: there the price is written without currency signs or formatting, and the old price doesn’t get in the way.

"price":\s*"?[\d.]+

Stock status is found by the text on the button or in the product card. Order matters: “Not in stock” must come before “In stock”, otherwise only the second part of the phrase is found. Use the case-insensitive command:

Not in stock|Out of stock|Pre-order|In stock

Or use the same markup, where availability is unambiguous: InStock, OutOfStock, PreOrder:

"availability":\s*"[^"]+"

Collecting service prices from competitor websites

Service websites rarely show “bare” numbers: “from $45/hour”, “starting at $120”. This pattern takes the price together with “from” or “starting at” and a unit, if there is one:

(?:from|starting at)\s*\$\d[\d,]*(?:\.\d{2})?(?:\s*(?:/|per)\s*(?:hour|hr|sq\.? ?ft|item|month))?

And this one takes only unit prices — per hour, square foot or item:

\$\d[\d,]*(?:\.\d{2})?\s*(?:/|per)\s*(?:hour|hr|sq\.? ?ft|item)

Run them with the case-insensitive command. This is a handy way to compare competitor price lists: service page URLs in one column, “from” prices in another.

Collecting contact details from websites

Email addresses on a page:

[\w.+-]+@[\w-]+\.[a-z]{2,}

Phone numbers are easiest to take from tel: links, where they are written without brackets and dashes:

tel:\+?\d+

WhatsApp and Telegram links:

https://(?:wa\.me|t\.me)/[\w.]+

An email often appears on a page twice: in a mailto: link and in the text. After splitting into columns, duplicates are easy to remove with the procedure that deletes repeated words. And a word on ethics: collect company contacts for your own work, not for spam, and respect privacy laws such as GDPR.

Getting product images

The main product photo is almost always in the og:image meta tag, which the site provides to social networks for link previews:

<meta property="og:image" content="[^"]+"

All images on a page are found by the img tag:

<img[^>]+src="[^"]+"

The match comes back together with the tag. The address itself is extracted with the REGEXEXTRACT function in Excel 365, which, unlike the procedure, supports lookbehind:

=REGEXEXTRACT(B2,"(?<=src="")[^""]+")

For og:image, replace src with content in the formula. If the address starts with a slash (/img/photo.jpg), add the domain in front of it with the procedure that adds text to the beginning of cells.

And now the nice part. In Excel 365 the IMAGE function shows a picture by its URL right in a cell: put =IMAGE(C2) next to the column of links, increase the row height — and you get a visual product catalog right in Excel.

=IMAGE(C2)

Counting product cards on a page (assortment)

To estimate a competitor’s assortment, count not the products but a repeating element of the product card. Open the source code of a category page, find the class of the card block (for example, product-card) and count it with the Count occurences command:

class="product-card

Don’t put the closing quote: cards often have extra classes, like “product-card product-card--sale”. If the site has structured data, you can count that too:

"@type":\s*"Product"

The procedure returns a number for each page. In a couple of minutes you see which competitor category has 12 products and which has 120. Keep in mind that only the first page of the category is counted: if products load with a “Show more” button or are split into ?page=2 pages, add those URLs to the list separately.

Why scraping doesn’t find data: common errors

• Empty result, although the data is visible on the page. The data is loaded by a script and isn’t in the source code. Check with Ctrl+U in a browser.
• Only part of the price is found: “990” instead of “12 990”. There is a non-breaking space or &nbsp; between the digits that the pattern doesn’t account for. Add it to the pattern explicitly.
• A pattern with “(?<=price)…” returns an error. Lookbehind isn’t supported in VBScript: match the data together with its “anchor” and remove the extra part with REGEXREPLACE.
• The site returns an error or an empty page for some requests. Its protection against frequent requests kicked in: process the list in parts, and check page availability with the Server response code procedure from the same menu.
• The original URL list is gone. Results are written in place of the cells: copy the URL column before running.

If you need to open the pages and look at them yourself, use the procedure that opens all links in a browser. And to get addresses out of hyperlinks, there is a procedure to extract URLs from hyperlinks.

Summary: what and how to scrape in Excel

I hope you found what you were looking for on this page and were able to set up web scraping in Excel. The Content by RegExp pattern procedure downloads every page from the list and extracts whatever the regular expression describes: prices and stock status, service prices, contacts, images. Count occurences helps estimate assortment, and the REGEXEXTRACT and REGEXREPLACE functions clean up the result.

If regular expressions are new to you, start with the article on regular expressions in Excel: it has a syntax cheat sheet and dozens of ready-made examples.