Need help w/ this function

%3CLINGO-SUB%20id%3D%22lingo-sub-1994825%22%20slang%3D%22en-US%22%3ENeed%20help%20w%2F%20this%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1994825%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20can%20someone%20help%20me%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20the%20LEN%20and%20LEFT%20functions%20nested%20in%20an%20IF%20function%20to%20convert%2010-digit%20length%20%23s%20in%20one%20column%20into%20shorter%205-digit%20%23s%20into%20another%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20that%20I%20was%20told%20to%20use%20was%20this%3A%3C%2FP%3E%3CDIV%20class%3D%22page%22%3E%3CDIV%20class%3D%22section%22%3E%3CDIV%20class%3D%22layoutArea%22%3E%3CDIV%20class%3D%22column%22%3E%3CP%3E%3CSTRONG%3E%3DIF(LEN(%5BZip%5D)%3D10%2CLEFT(%5BZip%5D%2C5)%2C%5BZip%5D%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Emaking%20the%20IF%20function%20tool%20box%20thing%20look%20like%20this%3A%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202020-12-16%20at%2012.04.24%20PM.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F241140iDEEE847A5BA551C0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202020-12-16%20at%2012.04.24%20PM.png%22%20alt%3D%22Screen%20Shot%202020-12-16%20at%2012.04.24%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EBut%20if%20I%20do%20this%20I%20get%20the%20%23SPILL%20error%20as%20shown%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202020-12-16%20at%2012.06.41%20PM.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F241141i3DB33EC29AD3A034%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202020-12-16%20at%2012.06.41%20PM.png%22%20alt%3D%22Screen%20Shot%202020-12-16%20at%2012.06.41%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20do%20I%20need%20to%20do%20in%20order%20to%20make%20this%20equation%20work%3F%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPLEASE%20HELP%20ME%20OUT!!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1994825%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1994955%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20w%2F%20this%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1994955%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F904142%22%20target%3D%22_blank%22%3E%40_emma_b_%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20seems%20you%20just%20missed%20the%20closing%26nbsp%3Bbracket%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(LEN(%5BZip%5D)%3D10%2CLEFT(%5BZip%5D%2C5)%2C%5BZip%5D%3CFONT%20size%3D%225%22%20color%3D%22%23FF0000%22%3E)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1994996%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20w%2F%20this%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1994996%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F904142%22%20target%3D%22_blank%22%3E%40_emma_b_%3C%2FA%3E%26nbsp%3BZIP%20seems%20to%20be%20part%20of%20a%20structured%20table%2C%20and%20Column1%20is%20was%20added%20to%20it.%20Try%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLEFT(%5B%40ZIP%5D%2C5)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E....%20since%20you%20just%20want%20the%205%20left%20most%20characters%2C%20whatever%20the%20length%20of%20the%20cell%20in%20ZIP%20is.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1995084%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20w%2F%20this%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1995084%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F904142%22%20target%3D%22_blank%22%3E%40_emma_b_%3C%2FA%3E%26nbsp%3BTried%20to%20edit%20my%20previous%20post%20but%20couldn't.%20I%20replicated%20your%20issue%20by%20adding%20a%20formula%20directly%20outside%20a%20structured%20table.%20See%20picture%20below%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-12-16%20at%2019.37.30.png%22%20style%3D%22width%3A%20291px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F241150i9E904E0DFC8117D8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202020-12-16%20at%2019.37.30.png%22%20alt%3D%22Screenshot%202020-12-16%20at%2019.37.30.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EColumn1%20becomes%20part%20of%20the%20table%20but%20you%20can't%20use%20such%20a%20formula%20within%20a%20structured%20table.%20You%20need%20the%26nbsp%3B%40-sign%20as%20suggested%20in%20my%20previous%20post.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1995089%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20w%2F%20this%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1995089%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BIt%20worked!%20Thank%20you%20so%20much%20for%20your%20help!!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E
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:

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