TEXTSPLIT not working #SPILL! or #NAME! error

Question

** edit ** Thank you for your answers everyone!! It was indeed converted to 'table',and since I reverted this, it is working. Excel seems to have done the conversionautomatically though, so I'll keep an eye out for that in the future. Thank you so much :)

Hi Community,

I'm having issues with the TEXTSPLIT in Excel (see screenshots for details).

I'm using Excel in Office 365 (desktop version, 2310).
It's fully updated and everything.

The suggested formula (from my work materials)
=TEXTSPLIT(E2, "/") returns a #SPILL! error

![](https://learn-attachment.microsoft.com/api/attachments/1d9cc8de-cbd9-4ba0-841c-7573f2f78041?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/1d9cc8de-cbd9-4ba0-841c-7573f2f78041?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">When I tried using: =TEXTSPLIT(TEXT(E2,"dd/mm/yyyy"),"/")
to circumvent the issue, it comes up with the #SPILL! error.

![](https://learn-attachment.microsoft.com/api/attachments/9cff51c9-8b46-413f-a030-e4794bb13f85?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/9cff51c9-8b46-413f-a030-e4794bb13f85?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">I have tried using Excel online through my 365 to see
if that makes a difference. The result is a different type of error - see
screenshot attached - it just muddles up all the numbers, and doesn't return proper dates.

![](https://learn-attachment.microsoft.com/api/attachments/0104105d-3367-460f-ad05-7058ef02cceb?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/0104105d-3367-460f-ad05-7058ef02cceb?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">The 'help' section in my Excel doesn't work either, it says 'waiting for network, try again later',
despite me being connected to the internet.

I'm at my wits end, really. I have a hunch that it might be a compatibility issue,
however I don't know enough about techy software things to determine what exactly is going on.
I bought my laptop in 2018 (refurbished), it has Windows 10, and I'm using Excel through Office 365, individual subscription.

Another possible 'fix' I found was logging out, checking for updates, and then logging back in to Excel once complete. It did indeed run an update (whereas before logging out it said 'all up to date, no updates necessary'), after which the TEXTSPLIT formula was gone. Luckily it returned when I logged back in, however errors still persist. Please advise!
Many thanks,
Z

Answer 1

You can't use spilling formulas in tables, which it appears your data is.

If all you are trying to do is to convert those to real dates, you might find using the Text to Columns option on the ribbon (Data tab) is simpler.

Answer 2

It is OK on my side.

Did you convert the range to table?

Answer 3

Hi. Although you "can", it's not very efficient. Also, remember that the split is on a numerical value, and not the display of "m/d/yr"

It's much more efficient to just add 3 columns manually with just Month(), Day(), and Year() in your table that reference column A.