Ever found yourself staring at a spreadsheet, wishing you could just… tidy things up? You know, merge a few cells of text, split a long string, or maybe just make sure everything's capitalized correctly? It’s a common scenario, whether you're importing data or diligently typing it in yourself. Thankfully, Google Sheets has a whole suite of text functions that can feel like a superpower, saving you heaps of time and frustration.
Let's start with something fundamental: turning numbers into text. Sometimes, you need a number to behave like text, especially when dealing with dates, times, percentages, or currency. The TEXT function is your go-to here. It’s pretty straightforward: you tell it the number (or a cell containing the number) and then the format you want it in. So, if you have the time 22:30 and want it displayed in a 12-hour format with AM/PM, you’d write =TEXT(“22:30”,”hh:mmAM/PM”). Or, if cell A1 holds a number you want to show as a percentage, =TEXT(A1,”0%”) does the trick beautifully.
Now, what if you need to join text together? Think first and last names, or maybe a city and state. The CONCATENATE function is the classic way to do this. You just list the pieces of text or cell references you want to join. For instance, =CONCATENATE(A1:D1) will smoosh together whatever’s in cells A1 through D1. But wait, what if you want a space between those words? Easy. You just insert a blank space within quotation marks between your cell references: =CONCATENATE(A1,” “,B1,” “,C1,” “,D1). It’s like building a sentence, one word at a time.
For those times when you're joining a lot of text and want a bit more control, TEXTJOIN is a real gem. It’s similar to CONCATENATE, but it lets you specify a delimiter – that’s just a fancy word for a separator, like a comma or a space – and it can also handle arrays of data. Plus, you can tell it whether to ignore empty cells or not. So, if you want to join everything in A1 through C2 with a space, and you don’t want any blank spots showing up, you’d use =TEXTJOIN(”“,TRUE,A1:C2). It’s incredibly handy for creating lists or combining data from multiple rows or columns.
On the flip side, sometimes you need to break text apart. Maybe you have a full name in one cell and want to split it into first and last names. That’s where SPLIT comes in. You tell it the text you want to split and the character you want to split it by. So, =SPLIT(A1,” “) will take whatever’s in cell A1 and split it wherever it finds a space. You can even get specific about how it splits, like telling it to only split at a specific phrase.
Comparing text is another common task. Are two names exactly the same? Is a product code correct? The EXACT function is your simple answer. It compares two pieces of text and gives you a straightforward TRUE or FALSE result. =EXACT(A1,B1) will tell you if the content of A1 is identical to B1. It’s a quick way to check for perfect matches.
And then there are times when you need to change text, not just combine or split it. You could use Google Sheets’ built-in Find and Replace, of course, but what if you need to be more precise? Like changing a specific word only the third time it appears, or altering a section of text based on its position? That’s where REPLACE and SUBSTITUTE shine.
REPLACE is great when you know the exact position and length of the text you want to change. For example, if cell A1 has “William H Brown” and you want to change it to “Bill Brown,” you could use =REPLACE(A1,1,9,”Bill”). This tells Sheets to go to cell A1, start at the 1st character, replace 9 characters, and put “Bill” there. SUBSTITUTE, on the other hand, is more about finding a specific piece of text and replacing it. So, if you wanted to replace every instance of “old” with “new” in cell A1, you’d use =SUBSTITUTE(A1,”old”,”new”). You can even specify which occurrence to replace if there are multiple.
These text functions might seem a bit technical at first glance, but once you start playing with them, you’ll see how they can streamline your work in Google Sheets. They’re like having a helpful assistant who can quickly and accurately manage all your text data, leaving you more time to focus on the bigger picture.
