Textsplit and table

Question

I have a file in which I'm trying to use textsplit. What I'm trying to do works perfect that is until I put it into an Excel table. Here is what I'm referring to, the bottom screen shot is from a table. Is there any way to get the function to work in an Excel table? The formula I'm using is =TEXTSPLIT(A1,"x")

1x2x3	1	2	3
10x5x1	10	5	1
1x10x3	1	10	3
1x2x.5	1	2	.5
Column1	Column2	Column3	Column4
1x2x3	#SPILL!
10x5x1	#SPILL!
1x10x3	#SPILL!
1x2x.5	#SPILL!

Answer 1

I believe you asked a similar question earlier and the outcome was that you may NOT use a dynamic array function (i.e. one that spills into multiple rows and/or columns) inside a structured table.

Answer 2

Hello Sammary,

I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

The #SPILL! error you're seeing is because the TEXTSPLIT function is trying to spread out, or "spill", results across multiple cells, but it can't do that within the row constraints of an Excel table.

To use TEXTSPLIT within a table, you should ensure that there are enough empty columns to the right for the data to spill into. If there are other data or table headers directly adjacent to where you expect the TEXTSPLIT results to appear, you will get a spill error.

If you are trying to split text across multiple columns within a table, you might need to add additional columns to the table first. After adding the columns, use the TEXTSPLIT formula in the first cell of the row where you want to split the text, and it should then spill over into the newly added columns.

Here's what to do:

Add new columns to the table for each part of the split. If you're splitting a string into three parts, you need two additional columns. Use TEXTSPLIT in the first column where you want the data to split. Ensure no other data is blocking the cells where the split data will go. After these steps, the TEXTSPLIT function should work within your Excel table.

I hope this helps

Best Regards, Ibhadighi

Answer 3

Ashish, Once again a timely response, thank you.

I'm not sure what your response means. The input text is text and not an array unless Table is converting it to an array. Other than writing a set of complex formulas to isolate and split out the "x" can you think of something that would work. I have the complex formulas but was hoping the new functions would make the code easier to read and fix in the event someone without advanced skills takes over managing this spreadsheet.?

Thanks again

Answer 4

Ibhadighi,Thank you for your response. However, if you look at the example that I provided you will see that there are enough cells for the output of the function to put the array. The problem is with putting the function into a Microsoft table.

Answer 5

Hi,

Functions that return a spilled range cannot accept a Table as an input. The source should remain a range itself.