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})))
sandramacAsked:
Who is Participating?
 
McOzConnect With a Mentor 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)

Open in new window

0
 
barry houdiniCommented:
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
 
barry houdiniCommented:
My solution assumes you have Excel 2003 or earlier - if you are using Excel 200 go with cOz's suggestion......

regards, barry
0
 
barry houdiniCommented:
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.

All Courses

From novice to tech pro — start learning today.