Google SheetsRandomnessTutorials

Removing HTML from a Cell in Google Sheets

Last updated on: Published by: Remedy667 0

So, I do a lot of editing website data and HTML in Google Sheets. A lot of the time it’s a lot of old stuff that someone else populated, sometimes it’s been extracted from a vendor website, and it’s almost always in need of some sort of repair.

Recently I came across this beauty:

Excessive HTML in a Google Sheets Cell

Which made me think about a way that I would first need to wipe all of the HTML. Usually I would just do a Find and Replace for <br /> or <p> tags and knock them out one at a time, but as you can see from the image about. This is a particularly special case, and I found this solution over on Stack Overflow that I’d like to share here for my own personal use, and to hopefully help out anyone else who experiences this problem.

In the Stack Overflow post, they mention 2 ways of accomplishing this, but I’m only going go over one of them (and modify it a bit):

=REGEXREPLACE(A1,"</?[^<>]*>","")

So looking at what Google says about this tag (REGEXREPLACE(text, regular_expression, replacement)), it’s pretty easy to see what we’re doing. Here, text refers to the cell we’re searching for HTML in (here we used A1, but the cell in my example image would have been K14625), and replacement is telling it to be empty, but what’s going on in the regular_expression?

  • </? = Finds all lesser than symbols to make them the starting point
  • [^<>] = Negates the character class, causing it to match a single character not listed in the character class (greater than & lesser than).
  • *> = Finds all greater than symbols to make that the stopping point (the asterisk helps it to ignore everything after the greater than)

If you go back into the original post, you’ll see that I removed the \S+ in the middle which i believe tells it to ignore whitespace, but that wasn’t relevant for what I was trying to accomplish, and my expression seemed to work just fine. If you’re experiencing problems, try it with the \S+ which can be found using the link above.

Randomness

Counting in Google Sheets for SEO

Last updated on: Published by: Remedy667 0

So, I’m working on this project where I’m optimizing descriptions for a client using Google Sheets. I need these descriptions to follow a very specific format, and to be sure that they do… I’m using Google Sheets. I’m also using it because I’m going to be populating them through an import with a CSV file. It just makes sense to do it all in one place.

Here are my format restrictions:

  • Description #1 must be over 200 words (with other content being about 120 words, so 80)
  • Description #1 must contain a specific keyword 1%-3% times
  • and the meta description must be less than 160 characters

Pretty simple stuff, just the start of a description and a meta description to accompany it. Here is the information I have been provided:

  • Custom URL fragment
  • Unedited Description

Find the Keyword

So, first to figure out my keyword. To do this I’m going to take my URL fragment:

/tools/power-tools-and-accessories/drills-and-drivers/impact-drivers/

I know my keyword is going to be “Impact Driver”, but I need Google sheets to know that. So I’m going to use the following code to isolate this phrase. My URL fragment is in position A2, because A1 is the title I will need for my CSV import.

I’ll figure out a cleaner way to do this in the future, but for now I need 2 cells. In B2 I’ll add this formula to convert the dashes to spaces, and remove the final slash on the right…

=LEFT(SUBSTITUTE(A2,"-"," "),LEN(A2)-1)

It’s super important that we remove the final slash with LEFT, otherwise the formula in cell C2 would return a value of nothing. Because it is returning whatever information is provided after the final slash.

=trim(RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))),1)))

Size Up the Description

Now we should have a cell (C2) containing our keyword, impact drivers, and it’s time to check our unedited description to see if it’s formatting complies with the restrictions I mentioned earlier. So, to test this, I’m going to past the current description in position D2.

“DEWALT offers a full line of cordless impact drivers and wrenches for the professional user. Ranging in voltage from 9.6 volts to 20 volts, including the 12V MAX* and 20V MAX* impacts, DEWALT has the right cordless impact driver or impact wrench for completing a wide range of fastening applications. When looking for an impact driver or wrench with innovation, performance and durability to withstand the rigors of the jobsite, DEWALT cordless has you covered.”

This will ultimately become our description (D2) & meta description(H2) for this page in our website, but first we need to see how they stack up. In position E2, we’re going to see how many words are in our description, and to do this we need a pretty simple tag that will essentially just count up the spaces. 

=COUNTA(SPLIT(D2, " "))

The next formula for position F2 is going to tell us how many times our specific keyword is used. I’m using SUBSTITUTE because I have 2 words combined as a phrase to use as my keyword, and I’m using LEFT to remove the plural, “s” so that I can get the singular version of the word(s) as well. 

=COUNTA(SPLIT(SUBSTITUTE(D2,left(C2,len(C2)-1)," &&&&& "),"&&&&"))-1

Figure Out the Percentage

Then, G2 is where we will simply calculate the percentage. This column will need to be formatted as a percentage by either clicking the “%” button, or going through the Google menu to Format > Number > Percent.

=F2/E2

Meta Description Optimization

We know know all the information we need to modify this paragraph and make our description. So, the meta description should be pretty easy from here. Once you’ve got the description how you like it, copy and paste it (D2) into the meta description cell (H2). Then, in cell I2 place this formula:

=len(H2)

…which will tell you how long it is. Now all I have to do is chop up the copy until it’s just under 160 characters.

I know it’s a pretty roundabout way of explaining this, but this is how I’m going about it for this project, and like I said… I’ll likely figure out a better way of doing this in the future, and I’ll make sure to redirect people to that page when I do. Thanks for reading along, feel free to comment below with any hints or criticisms.

Projects

36 Days (of Type) Later

Last updated on: Published by: Remedy667 0

This year, kind of at the last minute I decided to join in on the fun of #36DaysofType. I did each letter based on a horror film, and I had a lot of fun… Also, to my surprise, I actually finished it on time.

I had a lot of doubts at first whether or not I would be able to take on such a challenge in a timely manner, but I did it. During this challenge I was able to complete 3 fonts I had been working on for a while, which also made me very happy.

In fact, I had so much fun that I immediately (also ridiculously last minute) joined in on the fun of the #100DayChallenge. I’m still figuring out a theme, but I’ve been messing with procreate animations, so it’s just been a bit of that so far.

Monster and horror influenced, of course… but I’m open to requests.

My original plan was to have a little poem that went with each letter, and for the most part I finished them (see my instagram page for info). I had hoped to put together a zine when I was finished.

I’m still planning on putting together a small zine, but it will depend on how much time I have to put it all together, now that I’ve started another massive project.

Either way, keep an eye out for this zine in the future. I haven’t decided on print or PDF yet, but either way any information will be available here. Talk to you soon.

Google SheetsTutorials

Finding and Fixing “Unwanted Dates” with Fractional Data in Sheets

Last updated on: Published by: Remedy667 0

So, I’ve been tasked with sorting a bunch of data that all has to do with measurements… the only problem is, some of it is fractional, some of it is decimal, and some of it is metric. Which creates a couple of separate issues but right now I’m just going to talk about checking for a specific character in a cell.

Decimal Conversion Chart

So, I’ve got this fancy chart for doing my conversions, and my plan is to use the FILTER function so that I can convert them all to decimal… because Sheets does some weird stuff with fractions. For this example I’m going to add the inch symbol to my fractional using this formula:

=A2&”””

This returns the contents of cell A2, and adds the inch marker to the right, like so.

Perfect. Right? What about the fractions of less that 31 that could be easily read as dates?

Highlighted Cells that give undesired result.

Now we’re just going to take the visually simplest route to figure out which cells need fixed, because they all sort of blend together. I’m going to use this formula:

=IF(SEARCH(“/”,CELL),””,””)

You don’t need the IF function, I just wanted any cells that contained the forward slash to return no result. Anything that doesn’t will error out, like so…

Use #VALUE! error to see cells that don’t fraction

So, now that we have those blocks sorted out we can use a different formula on them, to clear the year from their date formatting, and replace it with the inch mark.

=SUBSTITUTE(CELL,”/2020″,””)&””””

Giving us the desired result… and fixing the #VALUE! error…

The story goes on from here, as I begin sorting my blend of Fractional, Decimal and Metric numbers, but I won’t bore you with that for now.

Google SheetsTutorials

Removing Letters (or Numbers) from a Cell in Google Sheets

Last updated on: Published by: Remedy667 0

First off, I should say that this post should actually be called, how to omit any letter in Google Sheets. It’s another one I forget all the time, and searching the web leads to all sorts of things like macros and plug ins.

Ain’t nobody got time for that…

So here’s the solution that I use, with a brief explanation, and then a bit of real world usage. I figure it makes sense, because I’m trying to use it right now.

=join(“”,split(CELL , “WHAT TO SKIP” ))

What this does is basically says, SPLIT the cell at whatever character you type in the “WHAT TO SKIP” space. Which would normally put everything into a new cell, however, because it’s wrapped in a JOIN tag… it places it all in one cell. The two quote marks with nothing between make sure that your block of text goes uninterrupted.

So, here’s what I’m trying to do.

I have a bunch of part numbers (on the right), where the letters determine the part type, and the number determines the size… but for what I’m doing right now, I don’t care about the size. I only care about the type.

I’m using my code to remove the numbers from the block. Leaving me with the following information.

=countif(RANGE,CRITERIA)

I can now apply a COUNTIF tag to see how many times a certain type is used, and determine what I need to do from there.

In this case, in case anyone is wondering, I’m going to delete almost all of them until I can make all of them have the number one. Which is going to be pretty hand to hand, so that being said… I’m going to get back to it.

Google SheetsTutorials

Split & Trim with Google Sheets using ArrayFormula

Last updated on: Published by: Remedy667 0

The issue is white space.

=split(CELL,”DELIMITER”)

Using the standard SPLIT formula, to get an unwanted white space at the beginning of each cell. Now, I don’t know about you, but that drives me absolutely nuts, and I’m always forgetting the fix. Ultimately, running through this sequence… over and over and over…

In the following example, I’m attempting to use a formula to split the Category column (K), into its subcategories.

Using the standard SPLIT formula you can see, I’m getting an extra white space at the beginning of each cell in columns D, E & F.

=split(K8,”||”)

It seems that adding an extra space to the end of the delimiter would fix this, only that splits it at every space. I still try to do this sometimes, even though it always fails.

=split(K8,”|| “)

Using TRIM with the formula only returns the data up to the first SPLIT, though I frequently repeat this mistake.

=trim(split(K8,”||”))

We’re so close here, we’re just missing the ARRAYFORMULA function. (ArrayFormula) “Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.” So, in this case, it basically allows you to apply the TRIM function to each cell populated by your SPLIT.

=ArrayFormula(trim(split(K8,”||”)))

Google SheetsTutorials

Conditional Formatting in Google Sheets

Last updated on: Published by: Remedy667 0

I always forget this, even though it’s super simple. So, I figured the best place to put it so that i would remember… is here in my blog. I use this a lot when I’m working on all kinds of spreadsheets, and I use spreadsheets constantly when I’m working on fonts, and with Adobe inDesign for data merges.

…but we’ll get to that later…

Here’s a quick formatting trick to highlight an entire row in Google Sheets.

Allow me to explain:

From the Sheets menu, select: Format > Conditional Formatting, and a dialog box will open on right side of your spreadsheet. For this example we’re just going to use “Single Color“, which should be the default.

For “Apply to range“, I’m going to apply it to the entire sheet starting at the first position, A1, and take it to the last row and column… which is H500 for this sheet. So, it will look like this:

A1:H500

Next, we’re going to want change “Format cells if…” by clicking on the drop down, and changing it to “Custom formula is“. This is where our formula comes in. For this sheet it is:

=$G1=0

What I’m doing here is having Sheets check to see if anything in column “G” has a value is equal to “0“. I’m applying it to row “1“, because that is the first row affected under “Apply to range” (A1:H500) directly above. These row numbers need match, or else the effected (affected?) rows will be offset, and it will look like your formula did not work.

Here’s a bit of back story (context) for anyone interested.

I was photographing products for a website, and needed to know what products were not in stock at the warehouse before I went searching for them. I used conditional formatting to hide any item that had a quantity of “0” on my printout.

So, here, instead of highlighting… I’m going to be changing the formatting of my cells that are out off stock to match the background color.

Ridiculously simple, but I always forget how to do this. So, now that I’ve written this I should hopefully remember.