Forum Discussion

H-M-P's avatar
H-M-P
Copper Contributor
Apr 12, 2021

Extract text from between characters multiple instances in one cell

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.

Formulas and Functions

1 Reply

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Apr 12, 2021

    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)))

Resources