Explanation
In this example, we have comma-separated text in column B.
But the problem is that the numbers in columns E, F, and G aren’t reallynumeric values.
How can convert these numbers as text to actual numeric values?
Well, one option is to use the VALUE function.
a number, date, or time format) into a numeric value.
We can do that with the IFERROR function.
The final step is to replace the “x” with the original text value.
We can do that by simply repeating the original TEXTSPLIT formula.
We then usearrayinside the IFERROR and VALUE functions as above.
The key difference is that TEXTSPLIT runsjust one time.
TEXTSPLIT can split text into rows or columns.
VALUE Function
The Excel VALUE function converts text that appears in a recognized format (i.e.
a number, date, or time format) into a numeric value.
Normally, the VALUE function is not needed in Excel, because Excel automatically converts text to numeric values.
IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.
LET Function
The Excel LET function lets you define named variables in a formula.
