Last Week’s Solution

How did you go with last week’s tasks? Were you able to get something more efficient than mine?

https://docs.google.com/spreadsheets/d/1WpSd-83DRZpDhwoBpLWFNS8C5pyVfJGrIex_TdYWrNY/edit#gid=1277979536

1. Inventory (respective):

=IFERROR(IF(VLOOKUP(E4,A3:C6,2,0)<=0,“No”,“Yes”))

2. Employee Location:

=IFERROR(VLOOKUP(E12,A10:C15,3,0))

3. Employee pay bands:

=VLOOKUP(E21,$A$21:$B$25,0,1)

Grabbing those Bits and Pieces

A few posts ago, I was showing how you can use IMPORTHTML to enter a bunch of names into Google Sheets at a moment’s notice. Whilst completing that post, I had started work on the following week’s post regarding how IFs look. Whilst preparing the data for that post, I came across the credit score website and the information within it. I followed the IMPORTHTML procedure and received the following output:

It provided information into cells that data analysts tend to avoid (depending on the required analysis). In terms of database normalisation (form), it was unnormalised as cells contain various parts of data, i.e. A2 has three bits of data (Excellent, 853, and 1200). To best suit the data transformation, I thought I would show how to clean/transform this dataset so that you have individual values in separate cells.

From this example (above), I will be using the Experian categories and numbers (column B). This is the spreadsheet that I’ll be using:

https://docs.google.com/spreadsheets/d/1tUd0QSk05v2C7LUtQUC_LpBCT5SfT6SeBVuRR1UMaBs/edit#gid=0

I will be showing two methods for separating these strings and numbers; LEN, LEFT, RIGHT, and SEARCH, and SPLIT.

IMPORTHTML

First thing’s first, we need to import the data. So, let’s use our IMPORTHTML function. Remember what you need to type? First, check out the website: https://www.money.com.au/credit-score/what-is-a-good-credit-score

What kind of element are we looking for? Table.

This has imported the table on that website for us. We can leave this table where it is. But, let’s move below it and start the LEN & SEARCH method.

Method 1: LEN, LEFT, RIGHT, and SEARCH

Let’s create a blank table, matching the data we want; 5×4 table.

Rating

To retrieve the Rating information, we want to get the left side of cell B2. When we type in LEFT(B2) though, we only get the first character. So, we need to tell Google Sheets how many characters to return. Since the word ‘Excellent’ has 9 characters, we need to type in 9 into the function:

Can you see what the problem is going to be when I drag the formula down? Let’s see:

Not all of the ratings are 9 characters long. Let’s make this a dynamic formula for these different cases. This is where our SEARCH function comes in. SEARCH returns a number (if you complete it correctly). This screenshot is telling us that the character ‘:’ is the 10th character in cell B2.

To make sure that we don’t include that character in our Rating column, we should take 1 from the SEARCH function, leaving us with

=LEFT(B2,SEARCH(“:”,B2)-1)

Let’s check to see if this formula worked for the rest of the column:

Looking nice.

Ranges

Let’s now pull the ranges that each Rating can sit between. This time, I’m going to grab the information from the right side of the column as I don’t need the Rating words. Just like LEFT, RIGHT will show the last character of the cell:

We’re going to have to logic/maths this one through. Since I’m looking for everything after the colon, I’m going to see how many characters are in the cell. I can do this with LEN function:

The total character length of B2 is 19. Since RIGHT is taking the characters from the right-hand side, I will want to ignore all characters from the left side of the colon. So, I will use maths to figure out how many characters I need to remove:

1. LEN(B2) = 19

2. SEARCH(“:”,B2) = 10

1. – 2. = 19 – 10 = 9.

Eyeballing this, I can see that 800-1000 is 8 characters long, so the 9th character of RIGHT will give me the space between the colon and the 8. I will need to remove 1 from the function. This will leave us with:

=RIGHT(B2,LEN(B2)-SEARCH(“:”,B2)-1)

Low

To make things a little easier on us, let’s use the Ranges column from this LEN & SEARCH table. We’re going to use the same logic pattern that we did to get the Rating words, by using the hyphen character instead of the colon character:

=LEFT(A11,SEARCH(“-“,A11)-1)

High

Like the Low formula, we’re going to use the same logic for the Ranges for the High formula. The total character length of B11 is 8. Since RIGHT is taking the characters from the right-hand side, I will want to ignore all characters from the left side of the hyphen. So, I will use maths to figure out how many characters I need to remove:

1. LEN(B11) = 8

2. SEARCH(“-”,B11) = 4

1. – 2. = 8 – 4 = 4.

This means that the formula will be:

=LEFT(A11,LEN(A11)-SEARCH(“-“,A11))

Nice. That’s the full table. Let’s look at the next method.

Method 2: SPLIT

Split will output the formula as an array. This means that, given the number of separators that it is searching for, it will split the text over cells depending on those separators. …let’s have a look at an example.

Here is a string in a cell.

When I implement the SPLIT function beneath it, with semicolon separators, its output should be identical because there are no semicolon characters located in A27.

Let’s add a semicolon in there:

The text has now split over two cells where the semicolon is present. Let’s add another one:

Now a fourth:

You get the idea.

Rating and Ranges

We’re going to split the category and ranges using the following formula:

=SPLIT(B2,“:”)

We can now copy the formula down through the rest of the table:

Low and High

Similar to the logic above using the colon character, we’re going to use the SPLIT formula on the Ranges using the hyphen character.

=SPLIT(B20,“-“)

Nice. Now let’s finish the rest of the table:

Conclusion

Doing these kinds of processes allows you to quickly separate values in Excel/Google Sheets to perform accurate and analysis quickly. Imagine the time saved by not typing out all of the values and hair pulling by searching through Stack Overflow and annoying all those people by not RTFM before posting a question when you could have just used these formulas.

Homework

Split the following strings into columns by special characters (:, -, ‘,”):

1. Here are three states that begin with M: Michigan, Mississippi, and Maine
2. This was first said by Shakespeare: “To thine own self be true.”
3. D’Angelo Russell gets revenge on Bruce Brown: ‘Jokic ain’t there next to you today’
4. Warning Label: Warning: Choking Hazard – Small Parts. Not for children under 3 years
5. Michelle drives a Jaguar named ‘Jessie’; Sonya drives either a Porsche, Mercedes, or BMW named one of the following: ‘Samantha’, ‘Miranda’, or ‘Charlotte’
   1. Hint: You will need the following formulas: SPLIT, ARRAYFORMULA, and CONCATENATE.

Can you find the most efficient and aesthetically pleasing formula?