How to split a string into array of characters?

Question

Say there's a cell A1 in which the text content is "丟內樓某". How could I split the text into an array such as ["丟", "內", "樓", "某"]?

Since this is a string of Chinese characters and there's no blank within it as a delimiter, I don't think I can use the SPLIT function.

Is there any other means?

Google Sheets and Microsoft Excel have different feature sets. The [google-sheets] tag description explicitly states "Don't use it for the Google Drive Excel files editor." and the [microsoft-excel] tag description explicitly states "Questions with this tag should ONLY be about how Microsoft Excel interacts with a specific web application. General questions about Microsoft Excel are off-topic here but can be asked at Super User." Choose one platform and edit tags appropriately. — doubleunary, Commented Feb 7, 2023 at 9:38
@doubleunary, you are right. When I noticed that, they have already answered it both for google sheets and ms excel. — zzzgoo, Commented Feb 8, 2023 at 3:01

user290033user290033 · Accepted Answer · 2023-02-07 09:44:53Z

1

Another solution:

=regexextract(A1,regexreplace(A1,"(.)","($1)"))

Or:

=split(regexreplace(A1,"(.)","$1ζ"),"ζ")

Or:

=arrayformula(mid(A1,sequence(1,len(A1)),1))

answered Feb 7, 2023 at 9:44

user290033

1

To clarify: all formulas in this answer are for Google Sheets, and none of them will work as-is in Microsoft Excel.
– doubleunary
Commented Feb 8, 2023 at 8:45

Add a comment |

doubleunary · Accepted Answer · 2023-02-07 09:35:45Z

3

You can do that with a simple regexextract() by building the regex with rept(), like this:

=regexextract(A1, rept("(.)", len(A1)))

To learn the exact regular expression syntax used by Google Sheets, see RE2.

answered Feb 7, 2023 at 9:35

doubleunary

15.7k9 gold badges23 silver badges59 bronze badges

1

rept() works fine in Google Sheets.
– doubleunary
Commented Feb 8, 2023 at 8:40

Add a comment |

Glorfindel · Accepted Answer · 2023-02-07 09:33:14Z

2

I found a solution by Joshua Tzucker here:

=SPLIT(REGEXREPLACE(REGEXREPLACE(A1&"","(?s)(.{1})","$1"&CHAR(127)),"'","''"),CHAR(127))

Rather complicated, but it's still a one-liner.

answered Feb 7, 2023 at 9:33

Glorfindel

2,3495 gold badges19 silver badges28 bronze badges

Thank you, it seems not an elegant solution, especially since there's a CHAR(127) that looks hard-coded.
– zzzgoo
Commented Feb 8, 2023 at 3:16

Add a comment |

3 Answers 3