Forum Discussion

Help_I_need_somebody's avatar
Help_I_need_somebody
Copper Contributor
Jul 13, 2021
Solved

finden und extrahieren

Beschriftung Tante Erna               NN TAnte Gisa            NN Onkel Herman   FB/BG! Onkel Max Jodi                 NN Maler GmbH   NN Hans            NN Gert FB/UB! ...
excel
  • Lorenzo's avatar
    Lorenzo
    Jul 14, 2021

    Help_I_need_somebody 

     

    Based on the sample you provided. With range formatted as a Table (not mandatory) & Excel 365/Web

     

     

    One way in B2

    =LET(
    x,  {"NN","FB/UB","FB/BG"},
    f,  IFERROR(FIND(x,[@lettering]),FALSE),
    IF(SUM(f), XLOOKUP(TRUE, ISNUMBER(f), x), "")
)

    Corresponding sample attached

Lorenzo's avatar
Lorenzo
Silver Contributor
Jul 14, 2021

Help_I_need_somebody 

 

Based on the sample you provided. With range formatted as a Table (not mandatory) & Excel 365/Web

 

 

One way in B2

=LET(
    x,  {"NN","FB/UB","FB/BG"},
    f,  IFERROR(FIND(x,[@lettering]),FALSE),
    IF(SUM(f), XLOOKUP(TRUE, ISNUMBER(f), x), "")
)

Corresponding sample attached

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 14, 2021

Lorenzo 

I'd use check like SUM(--RIGHT([@lettering], LEN(x))=x) not to trigger on words like Hanna.

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jul 14, 2021

    SergeiBaklan 

    I don't disagree :)) I knew what I suggested didn't account for all scenarios, hence why I began with Based on the sample you provided

    So far we don't know if the OP runs XL 365 or not so let's see how this develops and we'll adjust if necessary

    Thanks for your input anyway