# Formula help

Hello all, need help modifying this formula below, I need to add if they value returned is #value or an error, then value will be 7.

=TRIM(RIGHT(LEFT(Sheet4!A116,FIND("SM",Sheet4!A116)-1),LOOKUP(10^6,RIGHT(LEFT(Sheet4!A116,FIND("SM",Sheet4!A116)-1),{1,2,3,4,5,6})+0,{1,2,3,4,5,6})))
###### Who is Participating?

Commented:
try:
``````=IFERROR(TRIM(RIGHT(LEFT(Sheet4!A116,FIND("SM",Sheet4!A116)-1),LOOKUP(10^6,RIGHT(LEFT(Sheet4!A116,FIND("SM",Sheet4!A116)-1),{1,2,3,4,5,6})+0,{1,2,3,4,5,6}))),7)
``````
0

Commented:
Is it OK if it's a text value (the 7)? If so try

=LOOKUP("zzz",IF({1,0},"7",TRIM(RIGHT(LEFT(Sheet4!A116,FIND("SM",Sheet4!A116)-1),LOOKUP(10^6,RIGHT(LEFT(Sheet4!A116,FIND("SM",Sheet4!A116)-1),{1,2,3,4,5,6})+0,{1,2,3,4,5,6})))))

regards, barry
0

Commented:
My solution assumes you have Excel 2003 or earlier - if you are using Excel 200 go with cOz's suggestion......

regards, barry
0

Commented:
Sorry garbled my reply above - I meant "If you are using Excel 2007 or later go with McOz's suggestion"

barry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.