Abstract
Transcript
In this video, we'll take a look at how to handle numbers with the TEXTSPLIT function.
One result of using the TEXTSPLIT function is that all output is text, and this can cause problems if you need numeric values.
Let me illustrate with an example.
In this worksheet, we have some comma-separated text in column B and the goal is to split this text into the 4 columns to the right with the TEXTSPLIT function.
When I configure TEXTSPLIT to split the data using a comma for the column delimiter, TEXTSPLIT returns the four columns as expected.
I can copy the formula down to do the same thing for all rows.
At first glance, everything looks fine. But if I try to sum the values in the last three columns with the SUM function, I get zeros.
Why?
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.
This is because the VALUE function itself throws an error when it can't convert a value to a number.
One way to solve this problem is to use the IFERROR function.
IFERROR is designed to trap errors in a formula.
It returns a normal result when the formula does not return an error and a custom result when a formula does return an error.
To use IFERROR in this case, I provide the original formula as the value argument. For the value if error argument, I'll use "x" for the moment so you can see how this works.
Now I'll update the formulas.
Notice we still have numeric values in the last three columns.
However, the first column is now "x".
This tells us that the VALUE is returning an error on this column, and IFERROR is replacing the error with an "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.
This formula works well, but it would be inefficient with a large amount of data, because we are running the same TEXTSPLIT operation twice.
One way to solve this problem is to use the LET function.
I'll explain how to do that in an upcoming video.
