Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

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})))
0
sandramac
Asked:
sandramac
  • 3
1 Solution
 
McOzCommented:
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now