Changing upper case to lower case for 3 letters of a word

[Milos]

Hello all,

Would it be possible to alter a whole column (as shown in column B) so that any upper case entries remain upper case for the first three letters of a word but all other letters change to lower case (as shown in column C). However this would only apply to particular cells within the column as specified by another column (as shown in column A).


[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Change[/TD]
[TD]Uppercase[/TD]
[TD]Lowercase[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Yes[/TD]
[TD]RUMCOKE[/TD]
[TD]RUMcoke[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Yes[/TD]
[TD]VODKALEMON[/TD]
[TD]VODkalemon[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Yes[/TD]
[TD]WHISKEYDRY[/TD]
[TD]WHIskeydry[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]No
[/TD]
[TD]RUMCOKE[/TD]
[TD]RUMCOKE[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]No[/TD]
[TD]VODKALEMON[/TD]
[TD]VODKALEMON[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]No[/TD]
[TD]WHISKEYDRY[/TD]
[TD]WHISKEYDRY[/TD]
[/TR]
</tbody>[/TABLE]


Thanks,

 

[BQardi]

Something like this in C2 drag down:

=IF(A2="Yes",UPPER(LEFT(B2,3))&LOWER(RIGHT(B2,LEN(B2)-3)),B2)

 

[Milos]

Thanks BQardi that is perfect! I was struggling with Upper an lower case formatting with the IF function.

I am wanting to specify this code a little more if possible. Do know how would alter the code so that the logical test part of the IF function ("Yes" for the example above) just has to contain the values "yes" and this function will work.

For example if there were a multitude of cells down column A the cells all contained different values such as "Yes", "Yesterday", YesserieBob" "Yesabel" and it would still pick up the value "Yes" and change the cell according to the IF function. I am trying to implement the SEARCH function to no avail.

 

[BQardi]

Yeah, should be something like this:
Replace:

=IF([COLOR=#ff0000]A2="Yes"[/COLOR],UPPER(LEFT(B2,3))&LOWER(RIGHT(B2,LEN(B2)-3)),B2)

with:

=IF([COLOR=#ff0000]IFERROR(SEARCH("yes",A2),0)>0[/COLOR],UPPER(LEFT(B2,3))&LOWER(RIGHT(B2,LEN(B2)-3)),B2)

 

[Milos]

Thanks again. Is there also a possibility for the function to outline more than one text option?

Lets say I added another word into column A so the options were Yes, No and affirmative. Could I rework the formula so that two groups of text to apply in this function ("yes" and "affirmative").

 

[BQardi]

Yes you can use the OR function:

=IF([COLOR=#ff0000]OR[B]([/B][/COLOR]IFERROR(SEARCH("yes",A2),0)>0[COLOR=#ff0000],IFERROR(SEARCH("affirmative",A2),0)>0[/COLOR][COLOR=#ff0000][B])[/B][/COLOR],UPPER(LEFT(B2,3))&LOWER(RIGHT(B2,LEN(B2)-3)),B2)

 

[Milos]

Legend, thanks man.

I am always so close with my coding but just a little mistake throws the whole thing off!

 

[Eric W]

Just to show that Excel usually has several ways of accomplishing something, here's another option:

=IF(SUM(COUNTIF(A2,{"*yes*","*affirmative*"})),UPPER(LEFT(B2,3))&LOWER(MID(B2,4,99)),B2)

 

[jamcall]

Had to laugh seeing a question with example data involving Rum & Coke being answered by BQardi (Bacardi)...

Cheers,
~ Jim

 

[BQardi]

Had to laugh seeing a question with example data involving Rum & Coke being answered by BQardi (Bacardi)...

Cheers,
~ Jim

Hahaha...
I'm so used to my username that I never even realized this.
That is soooo funny...