finden und extrahieren

New Contributor
Tante Erna               NN
TAnte Gisa            NN
Onkel Herman   FB/BG!
Onkel Max
Jodi                 NN
Maler GmbH   NN
Hans            NN
Gert FB/UB!
Barabara   FB/NN
Alex NN
August                  NN


Aus dieser Tabelle möchte ich "NN" und "FB/UB" und FB/BG! finden und in eine weitere Spalte schreiben, kann mich da jemand unter

5 Replies
best response confirmed by Help_I_need_somebody (New Contributor)



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




One way in B2

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

Corresponding sample attached

@L z. 

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

@Sergei Baklan 

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

Thank you, it´s great result
You're welcome. Thanks for posting back & nice day...