Need help w/ this function

Occasional Contributor

Hey can someone help me,


I am trying to use the LEN and LEFT functions nested in an IF function to convert 10-digit length #s in one column into shorter 5-digit #s into another column. 


The formula that I was told to use was this:



making the IF function tool box thing look like this:Screen Shot 2020-12-16 at 12.04.24 PM.png

But if I do this I get the #SPILL error as shown

Screen Shot 2020-12-16 at 12.06.41 PM.png


What do I need to do in order to make this equation work???



8 Replies



It seems you just missed the closing bracket



@_emma_b_ ZIP seems to be part of a structured table, and Column1 is was added to it. Try this:




.... since you just want the 5 left most characters, whatever the length of the cell in ZIP is.

@_emma_b_ Tried to edit my previous post but couldn't. I replicated your issue by adding a formula directly outside a structured table. See picture below:

Screenshot 2020-12-16 at 19.37.30.png

Column1 becomes part of the table but you can't use such a formula within a structured table. You need the @-sign as suggested in my previous post.


@Riny_van_Eekelen It worked! Thank you so much for your help!!!!


Alternatively pressing undo twice when the formula was input would have reversed the expansion of the Table and leave the formula to operate as a dynamic array formula.  That said, it is probably better practice to leave at least one column  between the table and any array formula.

@Peter Bartholomew 

What's the need of spilled array in such case? IMHO, for the multi-columns table with linked calculations and different conditional formattings adding of another link on/from the spill only complicates the things. 

@Sergei Baklan 

Hi Sergei

I agree. Though I normally try to separate source data (Tables) from subsequent calculation (Dynamic Arrays), here, little is achieved by detaching the short zip-codes.  It does nevertheless offer an alternative route to removing the #SPILL! error which can prove useful at times.  Removing the gaudy stripes and filter buttons complete the look.

@Peter Bartholomew , sorry for my comment, I know what you know. Unfortunately separating data source, data preparation and reporting on three different parts is not commonly accepted.