Remove Repeated Characters from the End of Strings in Google Sheets

Are you looking for a solution probably a regex regular expression to remove repeated characters from the end of strings in Google Sheets? Then I have the answer in this post!

Let me make you understand my point first. When we combine two or more values, to specify the boundary, we normally use the characters like a ~ (tide), | (pipe), / (forward-slash), ^ (caret), etc. Such characters/signs are called delimiters.

When there are blank cells, there would be extra delimiters in the combined string. See the below pattern which I am talking about.

Combined the Columns B to E in F (B2:E6 in F2):

Unwanted Characters at the End of a String in Google Sheets

I want to remove those unwanted consecutive tide characters (underlined) from the end of the string, not from any other part.

This string/delimiter may be surrounded by white space or not, that depending on the method that we use to combine the columns.

In the above example, the delimiters have been surrounded by white spaces (a single white space on both the side).

Even though they are looking consecutive, I mean the repeated ~ delimiters, they are not because of the white spaces.

To make the process more complicated we are unsure about the number of repeated characters at the end of the strings as it purely depends on the blank cells.

Considering all the above, how to remove the repeated characters from the end of the strings in Google Sheets? I want a clean result as shown below.

After Removing Unwanted Characters from the End of a String

RegexReplace to Remove Repeated Characters from the End of Strings in Google Sheets

Combine Columns and Placing Delimiters as the Boundaries

Even though this tutorial is not related to combining columns, still, I am giving you the formula.

As per the above example, you may either use the formula 1 below in cell F2 and copy-paste it until F6 or formula 2 in cell F2 alone to combine the range B2:E6.

Formula 1:

=B2&" ~ "&C2&" ~ "&D2&" ~ "&E2

Formula 2:

=ArrayFormula(B2:B6&" ~ "&C2:C6&" ~ "&D2:D6&" ~ "&E2:E6)

There is also one dynamic formula for this. Instead of combining individual columns, we can use the array/range B2:E6 for combining columns.

See that flexible array formula to join columns below which to be simply keyed in cell F2.

Formula 3:

=ArrayFormula(transpose(QUERY(transpose(B2:E6&" ~ "),,9^9)))

Time to go to the formula that can remove repeated characters from the end of strings in Google Sheets.

Formula to Remove Consecutive Delimiters from the End of Strings in Google Sheets

As far as I know, there are three functions for replacing part of a string in Google Sheets. They are REPLACE, SUBSTITUTE, and REGEXREPLACE. The function REGEXEXTRACT will also work to a great extend.

But the best function to use to remove repeated unwanted characters from the end of a string in Google Sheets is none other than the REGEXREPLACE. Do you know why?

While the REPLACE requires the position and length of the characters to be removed, in the SUBSTITUTE there is no argument to specify the end of the string.

So a regex pattern matching is an ideal solution to remove unwanted characters from the end of a text in Google Sheets.

Either use the below formula 4 in cell G2 and copy-paste it until cell G6 or use the formula 5 in cell G2 alone.

Formula 4:

=REGEXREPLACE(F2,"(\s\~\s){1,}$","")

Formula 5:

=ArrayFormula(REGEXREPLACE(F2:F6,"(\s\~\s){1,}$",""))
Removing Consecutive Delimiters from the End of Texts in Google Sheets

Needless to say, we can replace the cell reference F2 in formula 4 with the formula 1 as well as the range reference F2:F6 in formula 5 with the formula 2. So that we can make column F blank.

We can’t replace the range F2:F6 with formula 3. I’ll come to that later that at the conclusion part. It’s not important at this point in time.

Formula, Especially Regular Expression, Explained

In the above example, I have used ~ sign as the delimiter (the boundary of each column).

While combining columns I have placed white space on each side of this delimiter as " ~ ". Please see the formula 1 or 2.

Here is the regular expression which we have used to remove the repeated characters (tide signs) from the end of the strings in the formulas 4 as well as 5 in Google Sheets.

(\s\~\s){1,}$

See the explanation below.

\s – matches 1 white space character.
\~ – matches 1 ~ character.
{1,} – (quantifier) matches 1 and unlimited times.
$ – position at the end of the string.

I have used \s both the side of the \~ to match the characters " ~ ".

If you don’t have white spaces, for example, see this text Country 1~Country 6~Country 11~Country 13~~, use the regular expression as (\~){1,}$.

The REGEXREPLACE formula replaces the above pattern matching characters with the ""character.

As per the above example, here are the regular expressions to remove " ^ ", " / ", and " | " repeated characters from the end of the strings in Google Sheets.

(\s\^\s){1,}$
(\s\/\s){1,}$
(\s|\s){1,}$

Conclusion

I have unattended one formula, i.e. the formula 3, that I have used to combine the columns. Here is the reason.

Using REGEXREPLACE it’s not easy to remove the unwanted delimiters at the end of that formula output. Because that formula places an irregular number of white space characters around the delimiter.

As a solution, slightly modify the formula 3 as below and then use the regular expression (\~\s){1,}$.

Formula Leaving Irregular Space with Delimiter

Further, if you use the TEXTJOIN function to combine values from multiple cells or range, then see the first post under ‘Resources’ below.

That’s all. Enjoy!

Resources:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.