Favorite functions: Text manipulation

Sooner or later, if you have a long string of text, you might want to actually do something with it. There are all sorts of things that can be done with a string of text! But if you go browsing through all the functions in the “text” category then you’re likely to be overwhelmed with functions you can’t see any apparent use for. Some of them I can’t see any use for, either! But even the ones I do use could be overwhelming if they were all introduced at once. So in this post I’m going to introduce you to just my favorite functions for manipulating text.

Absolute favorites: These are the tools at the “top” of my toolbox, as it were. There’s probably a 75% chance I’ll use them if I’m building a spreadsheet that manipulates text.

  • Concatenate: Putting together strings of text. For example, if you had one cell that contained “This is “, and another that contained “a complete sentence.”, you could use concatenate to create a cell that contained “This is a complete sentence.”
  • Mid, left, right: Pulling strings of text apart. For example, if you had a cell which had “abcdefg” you could use these functions to get part of that string, such as “cde”, “abc”, or “defg”.
  • Trim: Removing excess spaces. For example, if you had a cell with ”     This      is a      sentence.      ” then you could use this function to strip away all the leading and ending spaces, and reduce all the middle spaces to one, thus giving you “This is a sentence.”

Secondary favorites: These are tools that I use frequently, but not as frequently as my absolute favorites. Some of them are only useful when I’m creating a tool that will be used over and over, instead of just a one-off tool.

  • Repeat: Creating a string with a repeating sub-string. For example, using this function you could make a string of 3 X’s, or even 20 or 100 X’s. It’s mostly useful in conjunction with other functions such as concatenate or replace.
  • Replace, Substitute Replacing all occurrences of one sub-string with another.For example, you could replace “sentence” with “phrase” to turn “This is a sentence.” Into “This is a phrase.”
  • Upper, Lower, Proper Changing the case of a string. For example, you could produce “THIS”, “this”, or “This”, regardless of what case the original string was in.