TEXTSPLIT with numbers

Let me illustrate with an example.

I can copy the formula down to do the same thing for all rows.

At first glance, everything looks fine.

This happens because TEXTSPLIT always returns text.

One possible solution is to use the VALUE function to convert the numbers.

I can do that by wrapping the entire formula in inside the VALUE function.

Notice this works for the numbers.

We see that the SUM function is now working properly.

But notice I now have a #VALUE!

error in the first column.

One way to solve this problem is to use the IFERROR function.

IFERROR is designed to trap errors in a formula.

To use IFERROR in this case, I provide the original formula as the value argument.

Now I’ll update the formulas.

Notice we still have numeric values in the last three columns.

However, the first column is now “x”.

So, we’ve made some progress.

But how can we tell IFERROR to use the original value, instead of “x”?

The trick is to use the TEXTSPLIT function again.

I’ll simply copy the original formula, and paste it into IFERROR as the second argument.

Now when the VALUE function returns an error, IFERROR traps the error and returns the original value.

The final result is numbers where we want numbers, and text for the other values.

One way to solve this problem is to use the LET function.

I’ll explain how to do that in an upcoming video.