SPILL Help

Hi,

I've not really come across the spill error before but I was wondering if you might know a quick fix for an error I'm having. I want to know what position 1 of 6 words comes in from a long string in a cell. Only 1 word of the 6 will ever appear at a time.

Please Login or Register  to view this content.

The code "works" by telling me it starts from the 16th character of the string. However I have #value!'s in a "spill" above and below it which means I can't use it in a formula

I can shrink the spill by using UNIQUE as shown below. This gives me smaller spill with a cell saying #value! and the 1 below saying 16

Please Login or Register  to view this content.

Is there code which can remove the #Value! would solve an issue I'm having so easily!

How about
=AGGREGATE(15,2,SEARCH($AS$3:$AS$8,J8),1)

Absolute hero.

Didn't know about that bad boy. Thank you!!

You're welcome & thanks for the feedback.