# Need help w/ this function

Occasional Contributor

# Need help w/ this function

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.

=IF(LEN([Zip])=10,LEFT([Zip],5),[Zip]

making the IF function tool box thing look like this:

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

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

8 Replies

# Re: Need help w/ this function

It seems you just missed the closing bracket

=IF(LEN([Zip])=10,LEFT([Zip],5),[Zip])

# Re: Need help w/ this function

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

``=LEFT([@ZIP],5)``

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

# Re: Need help w/ this function

@_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:

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.

# Re: Need help w/ this function

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

# Re: Need help w/ this function

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.

# Re: Need help w/ this function

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.

# Re: Need help w/ this function

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.

# Re: Need help w/ this function

@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.