Share via

Textsplit and table

Anonymous
2023-12-11T23:55:22+00:00

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!
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-12-13T13:44:26+00:00

    Hi Sammary,

    Thank you for reaching out. My name is Leo, a user just like you. I'd be happy to help you with your concern.

    May I know which Microsoft Office version you are currently using? The TEXTSPLIT function is not a native Excel function, and the behavior you are experiencing with the #SPILL! error suggests that your version of Excel does not support dynamic arrays. Dynamic arrays allow a single formula to spill over into multiple cells to display all the results. This feature was introduced in Excel 365, Excel 2019, and Excel 2016 with the subscription version. If you are using an older version of Excel or a standalone version without a subscription, you may not have access to this feature.

    Your understanding and patience will be highly appreciated. I hope I provided a helpful information regarding your concern! Let me know if you have any further questions. Be safe always!

    Best Regards,

    Leo

    0 comments
  2. Anonymous
    2023-12-13T02:51:11+00:00

    I guess this is the behaviour of Excel table.

    Only Ranges are supported the return from textsplit.

    0 comments
  3. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2023-12-13T01:44:06+00:00

    The TEXTSPLIT function returns a spilled array and therefore the input to this function should be a range (not a Table)

    0 comments