To add/insert a delimiter into a text string after n or every n character in Google Sheets we can use one of the text functions in it. It’s none other than the REGEXREPLACE function.

Adding delimiter into a text string will be useful in several ways in Google Sheets including;

1. Split a text at every n character.
2. Split a text after n character.
3. Insert space, comma, pipe or any other separator into a text after every n character.
4. Insert a separator (any separator like a comma, pipe, etc.) into a text after ‘n’ character.

We may need to use two REGEXREPLACE formulas to add/insert delimiters as mentioned above. Here are those generic formulas.

Generic Formula 1 (To Add Delimiter After Every N Character)

=REGEXREPLACE(cell_ref,".{n}", "$0,")

Generic Formula 2 (To Add Delimiter After N Character)

=REGEXREPLACE(cell_ref,"^.{n}", "$0,")

I’ll explain to you how to use these two formulas independently (to insert delimiters) as well as with the SPLIT function (to split the string) in detail below.

How to Insert Delimiter into a Text String After Every N Character in Google Sheets

Assume cell A1 contains the below string (English alphabets A to Z).

ABCDEFGHIJKLMNOPQRSTUVWXYZ

To insert a comma after each character, use the following formula in cell B1.

=REGEXREPLACE(A1,".{1}", "$0,")

Want to see the result?

Here you go!

A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,

In the above formula, the number 1 within the curly brackets is the quantifier, which means ‘n’. Change the quantifier to 2 to insert a comma delimiter after every 2 characters.

=REGEXREPLACE(A2,".{2}", "$0,")

Result:

AB,CD,EF,GH,IJ,KL,MN,OP,QR,ST,UV,WX,YZ,

To change the delimiter from comma to a pipe, change the ‘replacement’ "$0," to "$0|". Then the formula outputs will be as follows.

A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|W|X|Y|Z|
AB|CD|EF|GH|IJ|KL|MN|OP|QR|ST|UV|WX|YZ|

As a side note, ‘replacement’ is the third argument in the REGEXREPLACE formula. Please refer to the syntax below.

Syntax: REGEXREPLACE(text, regular_expression, replacement)

This way we can add/insert a delimiter into a text string after every n character in Google Sheets.

Split at Every N Character in Google Sheets

When you want to split a text at every n character, you can use the above formula within SPLIT as below.

Since I have used the pipe symbol in the REGEXREPLACE as the delimiter, I must use the same as the delimiter within the SPLIT.

=split(REGEXREPLACE(A1,".{1}", "$0|"),"|")

How to Add Delimiter into a Text String After N Character in Google Sheets

By making only one change (caret sign addition) to the above formula, we can insert a delimiter into a text string after n character in Google Sheets. Please see one example.

The below formula is for inserting a hyphen character after the 13th character.

=REGEXREPLACE(A1,"^.{13}", "$0-")

In the next example below, the value in cell A1 is “InfoInspired”. To add a space delimiter after “Info”, i.e. after the 4th character, use the following REGEXREPLACE formula.

=REGEXREPLACE(A1,"^.{4}", "$0 ")

Result: Info Inspired

I hope now you can add/insert delimiter into any text after n character in Google Sheets.

Split After N Character in Google Sheets

Here also, using the SPLIT function with REGEXREPLACE, we can split any text after n character in Google Sheets.

The topic is about inserting delimiters aka separators into a text string. What about numbers instead of the text string?

Can I Similarly Add Delimiter into Numbers Using Regexreplace?

Sometimes we may have numbers in a column like product codes, employee IDs, etc. In such cases, the above formulas won’t work as it is. It may return a #VALUE! error.

Example:

Cell A1 contains the number 1256789 and I want to insert a hyphen after the 3rd digit. The formula as per our earlier example is =REGEXREPLACE(A1,"^.{3}", "$0-").

It would return the #VALUE! error. You can check the tooltip for the reason which would say;

Function REGEXREPLACE parameter 1 expects text values. But ‘1256789’ is a number and cannot be coerced to a text.

But there are workarounds to insert delimiters into numbers after n or every n character (here digit) in Google Sheets.

What are they?

There are two workarounds.

1. Format the number to plain text using the format menu (Format > Number > Plain text).
2. Alternatively, we can use the TO_TEXT function within REGEXREPLACE.

=REGEXREPLACE(to_text(A1),"^.{3}", "$0-")

Array Formulas to Add Delimiter After N / Every N Character in Google Sheets

Since the REGEXREPLACE is not an array formula, using ARRAYFORMULA function we can add delimiters into the text or numbers in a column at one go.

Here is the formula that you want to use when the values are numbers.

=ArrayFormula(if(A1:A="",,(REGEXREPLACE(to_text(A1:A),"^.{3}", "$0-"))))

Formula when values are text string. Here just remove the to_text around A1:A as;

=ArrayFormula(if(A1:A="",,(REGEXREPLACE(A1:A,"^.{3}", "$0-"))))

These array formula usage tips are applicable to all the formulas on this page.

Resources

• How to Use REGEXMATCH Function in Google Sheets.
• How to Use Regexextract Function in Google Sheets and RE2 Regular Expressions.
• Extract First, Last and Middle Names in Google Sheets (Formula Options).
• How to Split Number from Text When No Delimiter Present in Google Sheets.
• Extract Username from Email Address Using Regex in Google Sheets.
• Extract Numbers Within Square Brackets in Each Row in Google Sheets.
• Google Sheets LEFT, RIGHT Functions, and Alternative REGEXEXTRACT.
• Regex to Replace the Last Occurrence of a Character in Google Sheets.
• Regexmatch Dates in Google Sheets – Single/Multiple Match.
• Extract Numbers Prefixed by Currency Signs from a String in Google Sheets.
• Extract, Replace, Match Nth Occurrence of a String or Number in Google Sheets.
• Split Number to Digits in Google Sheets.
• How to Match | Extract Nth Word in a Line in Google Sheets.
• Extract Every Nth Line from Multi-Line Cells in Google Sheets.