Hey everyone! I’m working on a spreadsheet project and I’m stuck. I need to add letters to the end of words and change some vowels to their umlaut versions. Here’s what I’ve got so far:
I used CONCATENATE to add letters from one column to the end of words in another. Now I want to change a, o, and u to ä, ö, and ü. But only the first vowel should change if there are two in a row.
Some words don’t need umlauts based on another column. I’m not sure how to make the formula check this.
Here are some examples of what I’m trying to do:
- Uhren stays Uhren
- Doge becomes Döge
- Fluge becomes Flüge
- Hauser becomes Häuser
- Blatter becomes Blätter
Can anyone help me figure out the right formula for this? I’ve been messing with REGEXREPLACE and TEXTJOIN but I can’t get it quite right. Thanks!
I’ve encountered similar challenges in my spreadsheet work. One approach that’s worked well for me is using a combination of SUBSTITUTE and IF functions. Here’s a simplified version of the formula I’ve used:
=IF(NeedUmlaut=“Yes”, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, “a”, “ä”, 1), “o”, “ö”, 1), “u”, “ü”, 1), A1)
This checks if the word needs umlauts, then replaces the first instance of each vowel. It’s not perfect for all cases, but it handles most situations well.
For appending characters, you can wrap the above in another function:
=CONCATENATE(IF(…), B1)
Where B1 contains the characters to append. This approach has saved me considerable time in similar projects. Let me know if you need any clarification on implementing this solution.
I’ve tackled a similar project before, and it can be tricky! Here’s what worked for me:
For the umlaut conversion, I used a combination of REGEXREPLACE and IF statements. First, I created a helper column to determine if a word needed umlauts. Then, I used nested IF statements to check for the first vowel and replace it if necessary.
The formula looked something like this:
=IF(HelperColumn=“Yes”,
REGEXREPLACE(
REGEXREPLACE(
REGEXREPLACE(A1, “^([^aou])a", “$1ä”),
"^([^aou])o”, “$1ö”),
“^([^aou]*)u”, “$1ü”),
A1)
This checks the helper column, then replaces the first instance of a, o, or u with its umlaut version. It’s not perfect, but it handled most cases well.
For appending characters, I used a simple CONCATENATE function. Combining these two steps gave me the desired result for most words. Hope this helps point you in the right direction!
yo, i’ve dealt with this kinda stuff before. try using LAMBDA and MAP functions for more flexibility. something like:
=MAP(A1:A10, LAMBDA(x, IF(C1:C10=“Yes”, REGEXREPLACE(x, “^([^aou]*)([aou])”, LAMBDA(a,b,c, a&CHOOSE(MATCH(c, {“a”,“o”,“u”}, 0), “ä”,“ö”,“ü”))), x)&B1:B10))
this should handle both the umlaut swap and appending. goodluck!