How to Split a String Up into Individual Characters

Question

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                               

Answer 1

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).

Answer 2

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

Answer 3

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

Answer 4

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)),"","🍨"),"🍨"))

Answer 5

i think this can be done with a simple MID() formula.

=ARRAYFORMULA(MID(A2:A,SEQUENCE(1,MAX(LEN(A2:A))),1))