Regexreplace all lowercase words not working with german umlauts

Question

Trying this to replace all lowercase words is not working properly

=regexreplace(A1;"\b[a-züöäß]+\b";"")

Example sentence:

Mit Sätzen wie Gewinne laufen lassen Verluste begrenzen können vor allem weniger erfahrene Aktienkäufer oder Börseneinsteiger die wichtigsten Grundregeln des Aktienhandels kennenlernen und besser verinnerlichen.

also matches "ätzen" in "Sätzen" but Sätzen start with uppercase. Or matches "Aktienkäufer" to "Aktienk".

Tim Biegeleisen · Answer 1 · 2022-11-09T16:12:01.553

2

My guess is that the word boundaries are not working as expected because the German umlaut characters are considered non word characters. Try this version:

=TRIM(SUBSTITUTE(REGEXREPLACE(SUBSTITUTE(A1, " ", "  "), "(^| )[a-züöäß]+( |$)", ""), "  ", " "))

edited Nov 09 '22 at 16:12

answered Nov 09 '22 at 15:54

Tim Biegeleisen

502,043
27
286
360

Thx, but this is not working properly. This is not matching every lowercase word. – user3392296 Nov 09 '22 at 16:02
@user3392296 The regex pattern you want here is `(?<!\S))[a-züöäß]+(?!\S)`, but Google Sheets probably does not support lookarounds. I have edited my answer which should be closer now to your goals. – Tim Biegeleisen Nov 09 '22 at 16:16

score 1 · Answer 2 · answered Nov 09 '22 at 16:10

1

This is working

=INDEX(TEXTJOIN(" "; 1; LAMBDA(x;IF(REGEXMATCH(x&""; "^[a-züöäß]");;x))(SPLIT(A1; " "&CHAR(10)))))

answered Nov 09 '22 at 16:10

user3392296

644
4
16

score 1 · Answer 3 · answered Nov 11 '22 at 13:31

Your text includes german umlaut characters

You want to "replace all lowercase words"

We always use this simple formula

=REGEXREPLACE(G106," [a-züöäß]+","")

If you also want to omit the first word you can try

=REGEXREPLACE(G106,"^\w+ | [a-züöäß]+","")

It even works as an arrayformula

=INDEX(REGEXREPLACE(G106:G109,"^\w+ | [a-züöäß]+",""))

(Do adjust the formulae according to your ranges and locale)

Regexreplace all lowercase words not working with german umlauts

3 Answers3