Normalizing

Normalizing means to convert data to a predictable format.

This makes it easier to write validation and other code. Easy is good.

Let's see how you could do normalization while processing a Range.

Goats like sweaters

​Thanks to Teagan for the photo

Cthulhu is thinking about learning to knit. It tells its minions to survey some goaty friends, asking them which of three styles of sweater they like best:

• Goatneck
• Turtleneck
• Cardigan

Here's some of the data:

As you can see, the minions weren't careful about how they typed in the data. There are extra spaces everywhere. The capitalization is all over the place.

Hiding goat
Photo: Holly Vickery

Does a goat like goatnecks?

Suppose we want to test whether a goat likes goatneck sweaters. Because of all the variations, we would have to use an If like this:

Yuck! What a pain!

It would be easier to normalize the data, that is, convert it to one format. The If would be simpler, as we'll see.

The code pattern is something like:

So, grab data each record's data, and test it. But before testing it, normalize the data, so that testing is easier.

Counting favorite sweater types

Here's code that will count the number of each sweater type.

Lines 8 to 10 get the current record's data, with one variable for each field. Lines 14 to 21 looks at the sweater type in the current record, and adds to the appropriate count. However, before the counting code starts, lines 11 to 13 normalize the data. The sweater type in the current record is stripped of spaces, and made all lowercase.

So instead of...

... we can write...

M0000t! Much easier.

You can download the workbook, if you want to try it out.

What's stranger than goats in sweaters? Goats in trees!

Up next

We've normalized the data read from the Range. How about checking it for errors?