Formula Help

Hey guys, need some help, I got this formula from an expert here:
=IFERROR(SUBSTITUTE(SUBSTITUTE(INDEX(F115:L115,MODE(MATCH(SUBSTITUTE(SUBSTITUTE(F115:L115,"-",""),"+",""),SUBSTITUTE(SUBSTITUTE(F115:L115,"-",""),"+",""),0))),"-",""),"+",""),F115)

What it does is in F115:L115 find any matching data, ignoring the + or -.  In my latest set of data here is what i have
I115: is -TSRA, j115 is +TSRA and K115: is TS, so the result should be TSRA, but it is display the value of F115 which is blank.
sandramacAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
Hello Sandramac,

You can add another check to exclude blanks, i.e.

=IFERROR(SUBSTITUTE(SUBSTITUTE(INDEX(F115:L115,MODE(IF(F115:L115<>"",MATCH(SUBSTITUTE(SUBSTITUTE(F115:L115,"-",""),"+",""),SUBSTITUTE(SUBSTITUTE(F115:L115,"-",""),"+",""),0)))),"-",""),"+",""),F115)

confirmed with CTRL+SHIFT+ENTER

regards, barry
0
 
Saqib Husain, SyedEngineerCommented:
Since the other cells are blanks the blank matches with a blank and the result is a blank.

Although TSRAs also match they come after the blanks so the blank is displayed

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