11-24-2017 09:16 PM
I have a spreadsheet that several people use and would like to keep the text in a particular column always set to upper case. Regardless of case the user enters the data in this column it is saved in upper case. Is this possible? Thank you for your input.
Greg
11-25-2017 12:52 AM - edited 11-25-2017 12:55 AM
Greg,
This is possible, but with some lines of code!
I have a macro which is fairly good to do this task, to apply it follow these steps:
Step 1: Press Alt+F11 to open the VBA editor as shown in the below screenshot:
Step 2: Double-click on the sheet that contains the column you want.
Step 3: Copy this code to the editor, then close it.
Private Sub Worksheet_Activate() On Error Resume Next Application.ScreenUpdating = False Dim cell As Range For Each cell In Range("$A$1:" & Range("$A$1").SpecialCells(xlLastCell).Address) cell.Value = UCase(cell.Value) Next cell On Error GoTo 0 Application.ScreenUpdating = True End Sub
Step 4: Save the workbook as Macro-Enabled Workbook (*.xlsm).
After that, the macro will trigger each time you activate the worksheet.
NOTE: This macro is applied to column A in sheet1.
11-25-2017 02:02 AM - edited 11-25-2017 02:09 AM
You can use another way, it's forcing users from the beginning to enter uppercase characters through Data Validation feature.
Just follow these steps:
=EXACT(A1,UPPER(A1))
After that, users are not allowed to enter lowercase characters in column A.
NOTE: If you want to apply this to a column other than column A, select that column entirely, and change the cell references in data validation formula.
07-04-2019 01:02 AM
HI Haytham,
Hope you are doing well, I need one solution if it is possible;
I want to change a sentence written in a single cell to PROPER Case except the word(s) written in CAPITAL Letter.
For E:g 1,
From, "I love APPLE" To, "I Love APPLE"
For E:g 2,
From, "united arab emirates (UAE)" To, "United Arab Emirates (UAE)"
07-04-2019 09:46 AM
Hi,
I've already replied to your separate post.
Regards,
Haytham
07-04-2019 09:54 AM
This is a very special situation by the way...
The solutions provided by @Haytham Amairah are great.
However you want to treat the different words in the same cell differently!!
I suggest:
1- Select the range (assuming column A) that you want to fix >> Data Tab >> Text To Column
2- Data Tab >> Text To Columns (Should have blank columns to the Right) >> Check "Delimiter" >> Check "Space" >> Finish
3- In Cell D1 write the Formula that fixes the capitalization as desired
'=IF(NOT(EXACT(LOWER(A1),A1))=FALSE,PROPER(A1),A1)&" "& IF(NOT(EXACT(LOWER(B1),B1))=FALSE,PROPER(B1),B1)&" "& IF(NOT(EXACT(LOWER(C1),C1))=FALSE,PROPER(C1),C1)
4- Copy the formula down
5- Copy All results in Column D >> Then Paste Values (CTRL + C >> ALT E S V >> Enter)
6- Delete the Columns A, B, C
Hope that helps
Nabil Mourad
05-11-2020 01:27 AM
The VBA code is perfect - thanks. However, how can I get it to make all cells in 2 colums, e.g. A and C, uppercase, rather than just 1 column?
Thanks in advance for any help
05-13-2020 11:36 AM
Hi,
Please try the code on column C.
The code is somehow applied to all columns not only column A!
To limit it to column A & C, try this one instead:
Private Sub Worksheet_Activate()
On Error Resume Next
Application.ScreenUpdating = False
Union(Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row), _
Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)).Select
Dim cell As Range
For Each cell In Selection
cell.Value = UCase(cell.Value)
Next cell
Range("A1").Select
On Error GoTo 0
Application.ScreenUpdating = True
End Sub
11-06-2020 11:08 AM
Hi Haytham,
Am only able to add the data validation to one column.
When I add it to the next column, the next column does not work.
It only works for the 1st column created.
data validation --> formula custom = exact(a1,upper(a1)) works great
when i do same thing in next column, it does not work for that column
Any help is greatly appreciated.
Thank you,
Pam