Need help w/ this function

Copper 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:

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

 

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???

 

PLEASE HELP ME OUT!!!!

8 Replies

@_emma_b_ 

 

It seems you just missed the closing bracket

 

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

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

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

@_emma_b_ 

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.