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):
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.
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&" ~ "&E2Formula 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,}$",""))
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,}$.
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:
- How to Remove Extra Delimiter in Google Sheets – Join Columns.
- Replace Multiple Comma Separated Values in Google Sheets.
- Regex to Replace the Last Occurrence of a Character in Google Sheets.
- Extract, Replace, Match Nth Occurrence of a String or Number in Google Sheets.
- RegexReplace to Wrap Numbers in Brackets and Its Use in Query.
