Google-sheets – How to use a formula to split a text into individual characters, regardless of length

google sheetsgoogle-sheets-arrayformula

I have a list of words in Japanese. I want to write a formula that will input the word, e.g. ひらがな, and output it as an array of however many characters: ひ + ら + が + な, no matter the length of the word.

Below is an example of what I'm trying to do, where Column C contains the list of words I want to split up into individual characters.

enter image description here

I could just work out the length of the longest word and use a MID function to get characters at positions 1 to n, but I'm sure there must be a more beautiful and less messy way to do it. I found a solution online before, but for some reason I couldn't find it again, and I've forgotten how it worked. SPLIT and Text-to-columns both won't do anything because you need a specific delimiter character.

Best Answer

With a regex, you can replace each character with itself + ','.
Then use a regular split function.

=split(REGEXREPLACE(A1,"(.)","$0,"),",",true,true)