Dec 16 2020 10:09 AM
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:
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???
PLEASE HELP ME OUT!!!!
Dec 16 2020 10:28 AM
Dec 16 2020 10:32 AM - edited Dec 16 2020 10:36 AM
@_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.
Dec 16 2020 10:55 AM
@_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.
Dec 16 2020 11:04 AM
@Riny_van_Eekelen It worked! Thank you so much for your help!!!!
Dec 16 2020 12:01 PM
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.
Dec 17 2020 03:12 AM
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.
Dec 17 2020 02:05 PM
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.
Dec 19 2020 03:53 AM
@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.