What im working with

I have a list of product names, but unfortunately they are written in uppercase I now want to make only the first letter uppercase and the rest lowercase but I also want all words with 3 or less symbols to stay uppercase

im trying if functions but nothing is really working

i use the german excel version but i would be happy if someone has any idea on how to do it im trying different functions for hours but nothing is working

=IF(LENGTH(C6)<=3,UPPER(C6),UPPER(LEFT(C6,1))&LOWER(RIGHT(C6,LENGTH(C6)-1)))

but its a #NAME error excel does not recognize the first and the last bracket

CodePudding user response：

This is hard! Let me explain:

1. I do believe there are German words in the mix that are below 4 characters in length that you should exclude. My German isn't great but there would probably be a huge deal of words below 4 characters;
2. There seems to be substrings that are 3 characters in length but should probably stay uppercase, e.g. '550E/ER';
3. There seem to be quite a bunch of characters that could be used as delimiters to split the input into 'words'. It's hard to catch any of them without a full list;
4. Possible other reasons;

With the above in mind I think it's safe to say that we can try to accomplish something that you want as best as we can. Therefor I'd suggest

• To split on multiple characters;
• Exclude certain words from being uppercase when length < 3;
• Include certain words to be uppercase when length > 3 and digits are present;
• Assume 1st character could be made uppercase in any input;

For example:

Formula in B1:

=MAP(A1:A5,LAMBDA(v,LET(x,TEXTSPLIT(v,{"-","/"," ","."},,1),y,TEXTSPLIT(v,x,,1),z,TEXTJOIN(y,,MAP(x,LAMBDA(w,IF(SUM(--(w={"zu","ein","für","aus"})),LOWER(w),IF((LEN(w)<4) SUM(IFERROR(FIND(SEQUENCE(10,,0),w),)),UPPER(w),LOWER(w)))))),UPPER(LEFT(z))&MID(z,2,LEN(v)))))

You can see how difficult it is to capture each and every possibility;

• The minute you exclude a few words, another will pop-up (the 'x' between numbers for example. Which should stay upper/lower-case depending on the context it is found in);
• The second you include words containing digits, you notice that some should be excluded ('00SICHERUNGS....');
• If the 1st character would be a digit, the whole above solution would not change 1st alpha-char in upper;
• Maybe some characters shouldn't be used as delimiters based on context? Think about hypenated words;
• Possible other reasons.

Point is, this is not just hard, it's extremely hard if not impossible to do on the type of data you are currently working with! Even if one is proficient with writing a regular expression (chuck in all (non-available to Excel) tokens, quantifiers and methods if you like), I'd doubt all edge-case could be covered.

CodePudding user response：

Because you are dealing with any number of words in a cell you'll need to get crafty with this one. Thankfully there is TEXTSPLIT() and TEXTJOIN() that can make short work of splitting the text into words, where we can then test the length, change the capitalization, and then join them back together all in one formula:

 =TEXTJOIN(" ", TRUE, IF(LEN(TEXTSPLIT(C6," "))<=3,UPPER(TEXTSPLIT(C6," ")),PROPER(TEXTSPLIT(C6," "))))

Also used PROPER() formula as well, which only capitalizes the first character of a word.