Substitute the Nth Delimiter from Right in Google Sheets

Replacing a specific occurrence of a delimiter — especially from the end of a string — isn’t something Google Sheets supports out of the box. For example, if you want to replace the 2nd comma from the right in a list of tags or items, there’s no built-in function to do that.

In this post, you’ll learn how to substitute the Nth delimiter from right in Google Sheets using a smart formula-based workaround. Whether you’re dealing with comma-separated lists, pipe-delimited strings, or any other repeated delimiter, this method helps you target and replace a specific match counting from the end of the string — all with a single formula.

Table of Contents

Why SUBSTITUTE Alone Doesn’t Work

The SUBSTITUTE function in Google Sheets is great for replacing specific occurrences of a character — but it only counts from the beginning of the string.

Here’s the syntax:

SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

If you give it an occurrence_number, it will replace that Nth occurrence from the start — not from the end.

SUBSTITUTE from Start – Example

Let’s say cell A2 contains the following string:

Summer, Casual, Cotton, Blue, Men, T-Shirt, Large, Clearance

And you want to replace the 2nd comma with an asterisk. This formula would do the job:

=SUBSTITUTE(A2, ",", "*", 2)

Result:

Summer, Casual* Cotton, Blue, Men, T-Shirt, Large, Clearance

But what if you want to substitute the 2nd comma from the end instead?

Formula to Substitute the Nth Delimiter from Right in Google Sheets

To achieve this, we reverse the logic: instead of counting from the left, we calculate how far from the start the Nth from the right delimiter is.

General Formula:

=SUBSTITUTE(A2, ",", "*", LEN(A2) - LEN(SUBSTITUTE(A2, ",", "")) - n + 1)

Where:

A2 = your text string
"," = your delimiter
"*" = the replacement character
n = the Nth match from the end you want to replace

This works because:

LEN(A2) gives the length of the full string
SUBSTITUTE(A2, ",", "") removes all commas
The difference in length tells you how many commas (delimiters) exist
LEN(...) - n + 1 gives you the position of the Nth delimiter from the right, starting from the left

Example: Substitute the 2nd Comma from the Right

Let’s go back to our example in cell A2:

Summer, Casual, Cotton, Blue, Men, T-Shirt, Large, Clearance

This string contains 7 commas. If we want to replace the 2nd comma from the right, that’s the 6th comma from the left (because 7 – 2 + 1 = 6).

Formula:

=SUBSTITUTE(A2, ",", "*", LEN(A2) - LEN(SUBSTITUTE(A2, ",", "")) - 2 + 1)

Result:

Summer, Casual, Cotton, Blue, Men, T-Shirt* Large, Clearance

Make It More Readable with LET

To simplify and avoid repeating expressions, use the LET function:

=LET(
  delimiter, ",",
  replacement, "*",
  text, A2,
  n, 2,
  SUBSTITUTE(
    text,
    delimiter,
    replacement,
    LEN(text) - LEN(SUBSTITUTE(text, delimiter, "")) - n + 1
  )
)

This version lets you define delimiter, replacement, and n clearly, so it’s easier to adjust when needed.

Real-Life Example: Substitute a Delimiter from the Right in Product Tags

Let’s say you manage an online store and your product tag column contains text like this:

Summer, Casual, Cotton, Blue, Men, T-Shirt, Large, Clearance

Each comma-separated word is a tag. Now you want to highlight the third tag from the end — which is “T-Shirt” — for internal reporting or categorization.

Using the formula we discussed, you can substitute the 2nd comma from the right (the one before “Large”) with a special character, like a pipe (|), to make it stand out:

Result:

Summer, Casual, Cotton, Blue, Men, T-Shirt| Large, Clearance

This is especially useful if you’re preparing data for parsing, tagging, or automated processing.

Another Real-Life Example: Substitute Nth Comma in Name Strings

Consider a string like this:

Dr., John, A., Smith, Jr., PhD, USA

To split the name into the main identity (Dr., John, A.) and the trailing designations (Smith, Jr., PhD, USA), you might want to replace the 4th comma from the right — the one between “A.” and “Smith”.

Formula:

=SUBSTITUTE(A2, ",", "|", LEN(A2) - LEN(SUBSTITUTE(A2, ",", "")) - 4 + 1)

Result:

Dr., John, A.| Smith, Jr., PhD, USA

This helps in splitting or formatting names for official documents, personalization, or reports.

Wrap-Up

While Google Sheets doesn’t offer a built-in way to substitute the Nth delimiter from right, with a simple formula combining LEN and SUBSTITUTE, you can make it work — even for complex strings with many repeated characters.

Using this method, you can:

Target the Nth occurrence of a delimiter from the end
Use LET to keep formulas clean and readable
Handle real-world scenarios like tag lists, names, and structured data exports

This trick is especially handy when preparing data for splitting, transforming, or extracting key details from within long strings.