Formula's: Countwords & Getword

by Angelica Obi

There are different instances that we might want to use the countwords and getword formula. We might want to use them individually, and we might want to use them together.

Individually: Countwords

Say we have a column of addresses and we want to know how many individual words make up that address, we can use countwords to do this.

I don't know why you might want to count these words though 😅 but here's how:

Drag in your formula tool and then, in the configuration pane type in the formula CountWords ([whatever the column header is, in this case [Address])

In the data preview, we can see the number 4. This is because the address being read is 376 S Jasmine St. Countwords starts counting the content of a field from 1. This is really important to remember!

So 376 is word 1, S is word 2, Jasmine is word 3 and St is word 4.

Your results will look like this:

Individually: Getword

The Getword formula is used when we want to return a specific word from a sentence/field. Let's use the address example again. All the addresses in our dataset start with a number, so we want to get only the number part and not the rest and we can use our Getword formula to do this. Drag the formula tool onto the canvas. In the configuration pane, type in Getword ([Address],0).

*Inserts confusion*

This was my first road block. With Countwords, that first number of the address would be 1, however with Getword it starts with 0.

Tbh I think the developer just wanted to add a little bit of spice because I still haven't figured out whyyyyyyyyyyyyyyy.

Anyway, back to the example. When we out our formula in, this is what we get.

In the Getword column, it has given me exactly what I wanted.

Together: Countwords and Getword

The dreaded combination. If you want to be extra, and you need to use these tools together, here's how you do it. We will still use the addresses example but this time, we want the last part of the address. Some addresses end in street (St), others end in way. We can't use Getword alone because some addresses have "4" words and some have "3". Combining the two formula's together can help us achieve this.

First, we drag the formula tool onto the canvas as usual, then in the configuration pane we type:

GetWord([Address],CountWords([Address])-1)

We start with Getword because we want the formula to return a word, then we follow it with the column with our addresses as normal. Previously, we then put a number here to show the position of the word we want but as already mentioned, this won't work now. Instead we are going to put the countwords formula in its place and then include a -1.

This -1 accounts for the fact that getword and countwords start counting the words in a sentence differently. The minus indicated that we're counting from the end not the beginning, and then the 1 tells us that, according to countwords way of counting, we want the 1st word. So putting them together, we want the first word from the end.

Here is the result:

This is the result we want so yaaaay we got it right.

Just for the sake of argument, here's what happens if we use only getword but incorporate the -1. So if we typed GetWord([Address],-0) or even GetWord([Address],-1)

This returned the wrong part of the address. #FAILEDDDT

Anyway, I hope this blog has helped you. Ciao for now!

👋🏽

Mon 08 Aug 2022

1 mins read

Fri 25 Nov 2022

2 mins read

Fri 25 Nov 2022

Wed 23 Nov 2022