Using array formula, how to divide each character into a cell.
Input Output
cat c a t
dog d o g
horse h o r s e
tiger t i g e r
i think this can be done with a simple MID() formula.
=ARRAYFORMULA(MID(A2:A,SEQUENCE(1,MAX(LEN(A2:A))),1))
In regex, \B is not a word boundary. It matches in between strings, where strings don't contain word boundaries like spaces or hyphen -. Since SPLIT doesn't support regex or \b, we need to use regex to add a delimiter.
=ARRAYFORMULA(SPLIT(REGEXREPLACE("cat","\B","🍨"),"🍨"))
But you can ignore \B too and use a empty string:
=ARRAYFORMULA(SPLIT(REGEXREPLACE("cat","","🍨"),"🍨"))
If you ignore it, Before the SPLIT, it'll look like 🍨c🍨a🍨t🍨, whereas with \B, it'll look like c🍨a🍨t. A empty string matches [\b\B].
To use it with a array,
=ARRAYFORMULA(SPLIT(REGEXREPLACE(A2:INDEX(A2:A,COUNTA(A2:A)),"","🍨"),"🍨"))
Here's another possible solution.
=ArrayFormula(
Iferror(split(regexreplace(A2:A,
"(.)","$1❄️"),"❄️"),""))
This formula uses REGEXREPLACE to insert a placeholder character after each character, and it SPLITs by it.
ARRAYFORMULA - IFERROR - SPLIT - REGEXREPLACE
Use this formula
Just change the range A2:A with your own.
=ArrayFormula(LAMBDA(range, delimiter,
IF(range="",,SPLIT(REGEXREPLACE(REGEXREPLACE(range&"","(?s)(.{1})","$1"&delimiter),"'","''"),delimiter)))
(A2:A,CHAR(127)))
Using the delete control character "also called DEL or rubout", with the code 127. as a dilimiter in SPLIT that joined to every charachter with REGEXREPLACE: Replace "(?s)(.{1})", with "$1"&delimiter
Compact form
=ArrayFormula(LAMBDA(r, d,
IFERROR(SPLIT(REGEXREPLACE(
REGEXREPLACE(r&"","(?s)(.{1})","$1"&d),"'","''"),d),""))
(A2:A,CHAR(127)))
r Range
d Delimiter Char(127)
(?s) match the remainder of the pattern with the following
effective flags
(.{1}) 1st Capturing Group
. matches any character
{1} matches the previous token exactly one time
$ asserts position at the end of a line
Used formulas help
ARRAYFORMULA - LAMBDA - IF - SPLIT - REGEXREPLACE - CHAR
You can add a "named function" and take advantage of the build in recursion support.
"Data" -> "Named functions" -> "Add new function"
Set "Function name": e.g. To_CHAR
Set "Argument pplaceholder": e.g. s
Set "Formula definition":
= IF(
LEN(s) > 1,
VSTACK({LEFT(s)}, TO_CHARS(RIGHT(s, LEN(s) - 1) )),
s
)
Use it: = TO_CHARS(A3)
Optional:
To make your "Named function" more generic, add a 2nd argument that accepts a LAMBDA function for aggregation. In order to have multiple output formats (2) and (3).
Instead of a LAMBDA function you can use another "Named function", as seen in (4).
