Find Multiple Text Items within a Cell and return Text and Adjacent Characters

1
votes

Cell A Contains the following text on Line 1 and 2

Does the description contain CCLVL123456 If so, this is 3rd Party Does the description contain GCFAC12345 If so, this is 3rd Party

I want to find CCLVL or GCFAC and then return the entire data "CCLVL123456" or "GCFAC12345", the character lengths being return can be different

Here is the formula I started but can't figure out how to put an "OR" in the formula - also if neither is found the cell should be left blank

=(TRIM(LEFT(SUBSTITUTE(MID(A7,FIND("CCLVL",A7),LEN(A7))," ",REPT(" ",20)),20)))

1
excel-formula

1 Answers

0
votes

=(TRIM(LEFT(SUBSTITUTE(IF(FIND("CCLVL",A7),MID(A7,FIND("CCLVL",A7),LEN(A7)), FIND("GCFAC",A7),MID(A7,FIND("GCFAC",A7),LEN(A7)))," ",REPT(" ",20)),20)))