Category: Google Sheets

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

Posted on | by Remedy667 | Leave a Comment on Removing Letters (or Numbers) from a Cell in Google Sheets

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.

Split & Trim with Google Sheets using ArrayFormula

Posted on | by Remedy667 | Leave a Comment on Split & Trim with Google Sheets using ArrayFormula

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,”||”)))

Conditional Formatting in Google Sheets

Posted on | by Remedy667 | Leave a Comment on Conditional Formatting in Google Sheets

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.