Google Sheets: Selective character case changing

Does anyone know how to convert "selective" text or characters to lower or uppercase in Google Sheets? I know how to do it in Excel with a macro but have not been able to do it in Google Sheets.

Example;

I have an attendance sheet. People enter "w" if they worked. I want these entries to always be lowercase regardless of what case the user enters it in.

In the same sheets I want "s" when entered to always be uppercase.

Any help is greatly appreciated.

Thanks,
Mick.

Method 1: force the user to use the correct case

1. Go to the menu Data > Data Validation in Google Sheets.
2. Select the Range or column where you want all the entered texts to be in "UPPER" or "lower" case. The range for this example here is A1:A15.
3. Select the Criteria "Custom formula is".
4a. For "UPPER" case, enter the below formula.

Please Login or Register  to view this content.

4b. For "lower" case, enter the below formula.

Please Login or Register  to view this content.

5. Set other parameters: Select "Reject input" and "Show validation help text". Enter a message for "Show validation help text".
DONE!

Method 2: you select the range and then using an App Script that makes a new menu item for you, you switch the case as you need it

1. Go to Tools > Script editor
2. replace the basic code template with this code:

Please Login or Register  to view this content.

3. Click Save
4. Click Run
5. When prompted, click "Review Permissions"
6. Select your google account and continue
7. When prompted, click "Advanced"
8. Click "Go to Change Case App (unsafe)"
9. Click "Allow"
10. Once the script has been run successfully, you can close the App Script window
11. Reload the Google Sheet
12. You should now see a new menu item called "Change Case", after Add-ons and Help.

To use the feature, select the range you with to convert, then click "Change Case" and make your choice which case you want.

Method 3: Setup Data Validation so users must make a selection from what is available, and can not enter whatever they want

1. Go to Data > Data validation
2. Select the "Cell range"
3. Set Criteria to "List of items"
4. enter each possible option exactly how you want it
5. Select Reject Input
6. Select "Show validation help text"
7. Enter a suitable warning, like USE VALID SELECTIONS

This has the effect that if the list has w,x,y,z... but the user enters a capital W, it will automatically convert to lowercase... also, the cell will reject anything that is not in the list of options that you set

Method 4: using an App Script, this one automatically changes all entries, without warning, but unlike method 3, it allows users to enter any text they want

1. Go to Tools > Script editor
2. replace the basic code template with this code:

Please Login or Register  to view this content.

3. modify the variable to suit your needs:
sheetName = 'Sheet1'; // The name of the sheet to affect
colToChangeCase = 3; // The column number to affect: 1=A, 2=B, 3=C, etc, etc
rowNumber = 2 // The starting row number
4. UNcomment the correct row (UPPER or lower case) to suit your needs
5. Save the script, but do not run it, it is not necessary to run it, running it will just give you an error and confuse you
6. now go to your sheet and test data entry, for the above script it would be in cell C2, notice it will automatically convert to upper case, also, anything under it will also convert automatically