For a long time, this was quite a difficult problem that required a complicated array formula approach.
The big breakthrough, however, came when theREGEXEXTRACT function was introduced.
Why create an array of characters?
Why would you want to convert a text string to an array?
Basically, an array is a convenient container that you could feed into many other functions.
The newerTEXTSPLIT functionis designed to split text strings into arrays using a custom delimiter.
TEXTSPLIT works great for splitting text into words or splitting comma-separated text.
TEXTSPLIT will return a #VALUE!
As you’re free to see, this is a simple and elegant solution.
I recommend this approach if you have access to REGEXEXTRACT.
To understand this formula, we need to consider how the MID function is designed to work.
The trick is to ask the MID function for more than onestart_numsimultaneously.
Notice the final array is comma-separated, which corresponds to a horizontalarrayor range in Excel.
This formula is more transparent than the REGEXEXTRACT option.
It is possible to understand what the formula is doing step-by-step.
However, it is also significantly more complex.
There are two basic approaches.
The second approach is to use a more complex array formula.
Instead, the formulas must be manually copied into a range of cells big enough to hold all characters.
The INDIRECT function is a way of changing cell references as text into actual cell references.
This makes the formula unworkable if you need automation.
For an example, seeCount numbers in a text string.
For example, =MID(“apple”,2,3) returns “ppl”.
SEQUENCE Function
The Excel SEQUENCE function generates a list of sequential numbers in an array.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
LEN will also count characters in numbers, but number formatting is not included.
