I find myself routinely executing basic data process in an effort to make exported or gathered data more actionable.

My first two favorite basic functions and simple function combinations are:

1. Concatenate - concatenate(string1,string2,string3,...etc):
This joins strings! If its a number, turn it into a string[LPT: you can use text() in a pinch]. You can’t run calculation on joined numbers anyway, unless you get into arrays and thats a whole ‘nother post (that I will likely not write.)

Advertisement

Concatenate is useful if you’re dealing with incomplete exports - many times you’ll get relative paths (i.e. ‘/folder/file.htm’) and to complete the path you’ll need to append the absolute path.

=concatenate(‘http://website.com’, ‘/folder/file.htm’)

will return

http://website.com/folder/file.ht… - aaaaannndddd you’re done.

2. Combos: Left/Right/Len/Find -(value, count) -

Left(‘bananas’, 3) will return ‘ban’ -

Right(‘bananas’, 3) will return ‘nas’.

Len(‘bananas’) will return the length of the string (7).

Find’ returns the first value location of a string.

Find(‘n’, ‘bananas’) will return ‘3', thats the first location of N. There’s an additional argument you can include: ‘start position’...syntax: Find(value, string, start position)

Advertisement

Find(‘n’, ‘bananas’, 4) will return ‘5'. Even though we started looking for N starting at ‘anas’ the return value is still relative to the whole string not the starting position.

Combo use case time!!

Let’s say you’ve got a bunch of resolution data....like this:

1280x800
1366x768
320x568
768x1024

Let’s also say you want to separate the heigh from the width and in order to understand average height, which might be useful if you were making a dynamic sidebar. ::cough::

The first questions to ask ourselves about the data set is: ‘what’s consistent?’

I’ll tell you what! The ‘X’ clearly separates one from the other.

Identifying the format consistencies allow us to make actionable assumptions and incorporate logic to pare down/extract the data and achieve our end goals. We easily assume the consistent ‘X’ and as the resolution number length varies, we can’t simply throw a left() and grab the width.

How do you do it? We’ll need to combine left/right/len/find.

Let’s look at “320x480" -

Let’s find the “x” - since it’s our seperation point - find(“x”, “320x480") - this gives us “4"

Now we know 4 characters into the string is where we need to break up the party.

Remembering that left(data, count) will let us grab the leftmost characters from the data, let’s throw in the position of the ‘X’ by nesting the functions -

left(“320x480", (find(“x”, “320x480")-1))

(LPT - we have to subtract 1 from the ‘X’ location because we don’t want to INCLUDE the X, we want to use it as the breakpoint)

The above function will return “320" so we’re set on the width; now we’ll need to grab the rightmost part of the x separation.

This is a little cooler, since we’ll need incorporate more intelligence - we’ll need the Rightmost characters of the full data length stopping at the X separation point.

The X being the separation point and being inherently left defined leaves us saying give me everything from the right all the way up to the X - the character count now becomes full length MINUS the X location (since the X is counted from the left and is our separation we truncate at the point)

=right(“320x480", len(“320x480")-(find(“x”, “320x480")))

The above function gives us “480"

Now, given this data set:

1280x800
1366x768
320x568
768x1024

And these functions:

width column:

=left(DATA, (find(“x”, DATA)-1))

height column:

=right(DATA, len(DATA)-(find(“x”, DATA)))

You will end up with:

widthheight
320480
1280800
1366768
320568
7681024

Ok.

That’s all I got for you this time!