Why does the new TEXTSPLIT function in Excel return #N/A errors at the right side? When does a double space create a blank cell? Solve both problems using optional arguments for Ignore_empty and Pad_With.
Transcript of the video:
Prevent the #N/A from TEXTSPLIT by using the pad_with.
Do a TRIM with TEXTSPLIT by using ignore_empty.
So we have this new TEXTSPLIT function.
I'm going to split this into columns at each space and into rows at each period.
But there's some problems. Alright, first off, the data coming in has space space occasionally and those space space are showing up as empty cells.
To prevent those empty cells, we use the 4th argument, which is called ignore_empty.
Put a TRUE there and anytime there's a space space it will be seen as an empty cell and will ignore empties and so we get rid of the spaces.
But we still have this problem that some sentences are longer and other sentences are shorter and Excel is filling the empty cells at the right hand side with #N/A.
So what we're going to do here is use the thing called Pad_With.
Just with quote quote out there and at least it will look better.
Well hey, I want to thank you for stopping by.
We will see you next time for another netcast from MrExcel.
Do a TRIM with TEXTSPLIT by using ignore_empty.
So we have this new TEXTSPLIT function.
I'm going to split this into columns at each space and into rows at each period.
But there's some problems. Alright, first off, the data coming in has space space occasionally and those space space are showing up as empty cells.
To prevent those empty cells, we use the 4th argument, which is called ignore_empty.
Put a TRUE there and anytime there's a space space it will be seen as an empty cell and will ignore empties and so we get rid of the spaces.
But we still have this problem that some sentences are longer and other sentences are shorter and Excel is filling the empty cells at the right hand side with #N/A.
So what we're going to do here is use the thing called Pad_With.
Just with quote quote out there and at least it will look better.
Well hey, I want to thank you for stopping by.
We will see you next time for another netcast from MrExcel.
