I have a certain set of characters in a cell, for example ab123456789cdefgh...

and I want the formula to add a space every 3 characters up until the 12th character.

the result would be like so: ab 123 456 789 cdefgh...

I tried using =REGEXREPLACE("string",".{3}", "$0 ") but of course it works for the whole string.

What could I do to solve this?

CodePudding user response：

One way is to use capture groups like this:

=TRIM(REGEXREPLACE(" ab123456789cdefgh"&REPT(" ",12),"(.{3})(.{3})(.{3})(.{3})(.*)","$1 $2 $3 $4 $5"))

I've appended 12 spaces with REPT(" ",12) and wrapped the result in TRIM( ) as a failsafe, in case some string has fewer than 12 overall characters in the original. This will allow the intended return through as many characters as exist instead of returning just the original string (having found nothing that matched the REGEX sequence. In other words, with the appended spaces, it will always find the sequence.

CodePudding user response：

Split the string at the 12th character. Add space in the first part of the string using regex. Concatenate the string after regex and the second part of the string. You may have to check the length of the input string too.

CodePudding user response：

Try:

=ArrayFormula(TEXTJOIN(" ",1,MID(A1,{1,4,7,10},3),RIGHT(A1,LEN(A1)-MIN(12,LEN(A1)))))