Jul 13 2021 12:01 AM
Beschriftung |
Tante Erna NN |
TAnte Gisa NN |
Onkel Herman FB/BG! |
Onkel Max |
Jodi NN |
Maler GmbH NN |
Hans NN |
Gert FB/UB! |
Helmut |
Barabara FB/NN |
Alex NN |
HugoFB/UB |
Gert.NN |
Egon |
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
Jul 14 2021 10:53 AM
Solution
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
Jul 14 2021 11:09 AM
I'd use check like SUM(--RIGHT([@lettering], LEN(x))=x) not to trigger on words like Hanna.
Jul 14 2021 11:59 AM
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
Jul 19 2021 02:01 AM
Jul 14 2021 10:53 AM
Solution
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