mercredi 28 mars 2018

Excel MATCH text but return a value to left or right depending on other text

I have a terribly inelegant formula to return a value from the left or the right of a string depending on the MATCH. It works, but I am hoping that someone can help with making this a bit easier to read. The general read of the formula is:

  • If you find "; T" (the full text could also be "TestSpec;")
  • Then return the value to the Right
  • Else, return the value to the Left

My problems are:

  • The IF statement by itself can only return either the LEFT or the RIGHT
  • The MATCH statement returns a Boolean so it's not helpful in determining LEFT or RIGHT
  • 2 IFERROR statements

Here's the Excel Formula in all it's glory:

=IFERROR( 
   IFERROR(
     IF(MATCH("*; T*",Table1[@Tags],0), 
       LEFT(Table1[@Tags],FIND(";",Table1[@Tags])-1)
     ), 
     RIGHT(Table1[@Tags],LEN(Table1[@Tags])-FIND(";",Table1[@Tags])-1)
   ), \*First Error Catch*  
 "") \* Second Error Catch*

Aucun commentaire:

Enregistrer un commentaire