Extract text from between characters multiple instances in one cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2268135%22%20slang%3D%22en-US%22%3EExtract%20text%20from%20between%20characters%20multiple%20instances%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2268135%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20online%20forms%20database%20that%2C%20when%20exported%2C%20provides%20both%20the%20field%20label%20and%20value%2C%20such%20that%20many%20of%20the%20table's%20columns%20have%20redundant%20data.%26nbsp%3BI%20would%20like%20to%20eliminate%20the%20duplicated%20info.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%20I%20would%20like%20column%20A%20to%20go%20from%3A%3C%2FP%3E%3CP%3EGMC%20(GMC)%2C%20NH4%2B%20(NH4%2B)%2C%20NO3-%20(NO3-)%2C%20Other%20(Other)%3C%2FP%3E%3CP%3Eto%3C%2FP%3E%3CP%3EGMC%2C%20NH4%2B%2C%20NO3-%2C%20Other%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%26nbsp%3B%3DMID(LEFT(A1%2CFIND(%22)%22%2CA1)-1)%2CFIND(%22(%22%2CA1)%2B1%2CLEN(A1))%20but%20that%20returns%20only%20the%20first%20instance%20of%20text%20bordered%20by%20brackets%2C%20and%20I%20need%20every%20instance%20output%20into%20in%20a%20single%20cell.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2268135%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hi

 

I have an online forms database that, when exported, provides both the field label and value, such that many of the table's columns have redundant data. I would like to eliminate the duplicated info. 

 

Example: I would like column A to go from:

GMC (GMC), NH4+ (NH4+), NO3- (NO3-), Other (Other)

to

GMC, NH4+, NO3-, Other

 

I tried =MID(LEFT(A1,FIND(")",A1)-1),FIND("(",A1)+1,LEN(A1)) but that returns only the first instance of text bordered by brackets, and I need every instance output into in a single cell.

1 Reply

@H-M-P 

With Excel 365, you could search for parentheses and then use FILTER and the MID function.

= LET(
    k,    SEQUENCE(LEN(text)),
    strt, 1+FILTER(k,MID(text,k,1)="("),
    end,  FILTER(k,MID(text,k,1)=")"),
  TEXTJOIN(", ", , MID(text,strt,end-strt)))

image.png