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

MattKing · Answer 1 · 2022-09-26T11:27:40.870

2

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

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

edited Sep 26 '22 at 11:27

answered Sep 26 '22 at 11:21

MattKing

7,373
8
13

Whats wrong here [see this](https://i.imgur.com/yO9HlkE.png) – Osm Sep 26 '22 at 11:25
my bad i mis-copied a parenthesis. Fixed in Edit. @Osm – MattKing Sep 26 '22 at 11:27
Yeh the old way :) upped – Osm Sep 26 '22 at 11:28

TheMaster · Answer 2 · 2022-09-26T13:34:24.883

2

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 cat, whereas with \B, it'll look like cat. A empty string matches [\b\B].

To use it with a array,

=ARRAYFORMULA(SPLIT(REGEXREPLACE(A2:INDEX(A2:A,COUNTA(A2:A)),"",""),""))

edited Sep 26 '22 at 13:34

answered Sep 26 '22 at 11:32

TheMaster

45,448
6
62
85

This works perfectly, but one problem: if I didn't have , does it work ? – Osm Sep 26 '22 at 12:46
1

@Osm What you don't have ice cream? Go buy right now. It won't work without it. – TheMaster Sep 26 '22 at 13:36

Osm · Answer 3 · 2022-09-27T04:14:47.197

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

score 1 · Accepted Answer · edited Sep 27 '22 at 06:49

1

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

edited Sep 27 '22 at 06:49

Osm

2,699
2
4
26

answered Sep 26 '22 at 13:28

z''

4,527
2
3
12

How to Split a String Up into Individual Characters

4 Answers4

Linked