When you combine values from different cells or columns in Google Sheets, you may encounter a common issue – unwanted delimiters like commas, semicolons, or pipe symbols in the joined text. But fret not, my friends! I have just the solution to intelligently remove these extra delimiters and make your Google Sheets project look sleek and professional.
Removing Extra Delimiters Using Join Functions
To remove those pesky extra delimiters, we’ll steer clear of the SUBSTITUTE or REGEXREPLACE functions. Instead, let me show you the proper way to join columns and avoid additional delimiters like repeated commas.
But first, let’s take a look at the CONCATENATE function. While it’s great for joining values in arrays, it doesn’t support placing a delimiter between the joined text. It simply appends strings to one another. That’s where the JOIN function comes to our rescue!
The JOIN function is commonly used for joining texts with a specified delimiter. The delimiter can be any character, but we typically use commas or semicolons. You can even use the pipe symbol or caret. Let’s see how the JOIN function handles joining columns:
="Availability: "&join(", ",A3:E3)Result:
Availability: Cement, 5, , 5, 10Uh-oh! Did you notice that extra comma? That’s because of the blank cell in the range that we joined. But fear not, my friends! We have a solution.
Introducing the TEXTJOIN Function
To avoid those extra delimiters caused by blank cells, we can use another powerful function in Google Sheets – TEXTJOIN. With this function, you have the power to decide whether to include blank cells or not.
Take a look at the TEXTJOIN formula:
="Availability: "&textjoin(", ",TRUE,A3:E3)Result:
Availability: Cement, 5, 5, 10See how the result is free from any extra delimiters? That’s the magic of TEXTJOIN! By changing the TRUE value to FALSE, you can include blank cells if you prefer. But I recommend sticking to TRUE for cleaner output.
Conclusion
Before we part ways, please remember that JOIN and TEXTJOIN are different types of functions. JOIN only supports one-dimensional arrays, while TEXTJOIN is more versatile.
To summarize, here’s a quick comparison of text join functions in Google Sheets:
- JOIN: Appends strings without placing a delimiter.
- TEXTJOIN: Joins texts with a specified delimiter and the option to ignore blank cells.
So, my dearest friends, let’s bid those extra delimiters farewell and embrace the power of TEXTJOIN to create beautifully formatted Google Sheets! If you want to sharpen your Google Sheets skills even further, don’t forget to explore more helpful tutorials on Crawlan.com. Until next time, happy sheeting!
