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.)

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 |

Advertisement

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.

Advertisement

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"

Advertisement

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)*

Advertisement

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)

Advertisement

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

The above function gives us “480"

Now, given this data set:

1280x800 |

1366x768 |

320x568 |

768x1024 |

Advertisement

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:

width | height |

320 | 480 |

1280 | 800 |

1366 | 768 |

320 | 568 |

768 | 1024 |

Advertisement

Ok.

That’s all I got for you this time!