Home

Microsoft Excel keeping all cells uppercase

%3CLINGO-SUB%20id%3D%22lingo-sub-131187%22%20slang%3D%22en-US%22%3EMicrosoft%20Excel%20keeping%20all%20cells%20uppercase%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-131187%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20several%20people%20use%20and%20would%20like%20to%20keep%20the%20text%20in%20a%20particular%20column%20always%20set%20to%20upper%20case.%26nbsp%3B%20Regardless%20of%20case%20the%20user%20enters%20the%20data%20in%20this%20column%20it%20is%20saved%20in%20upper%20case.%26nbsp%3B%20Is%20this%20possible%3F%26nbsp%3B%20Thank%20you%20for%20your%20input.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGreg%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-131187%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-737743%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Excel%20keeping%20all%20cells%20uppercase%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-737743%22%20slang%3D%22en-US%22%3E%3CP%3EHI%20Haytham%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20you%20are%20doing%20well%2C%20I%20need%20one%20solution%20if%20it%20is%20possible%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20change%20a%20sentence%20written%20in%20a%20single%20cell%20to%20PROPER%20Case%26nbsp%3B%3CSTRONG%3Eexcept%3C%2FSTRONG%3E%26nbsp%3B%20the%20word(s)%20written%20in%20CAPITAL%20Letter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CU%3EFor%20E%3Ag%201%2C%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CEM%3EFrom%2C%3C%2FEM%3E%20%22%3CSTRONG%3EI%20love%20APPLE%3C%2FSTRONG%3E%22%20%3CEM%3ETo%2C%3C%2FEM%3E%20%22%3CSTRONG%3EI%20Love%20APPLE%3C%2FSTRONG%3E%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CU%3EFor%20E%3Ag%202%2C%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CEM%3EFrom%3C%2FEM%3E%2C%20%22%3CSTRONG%3Eunited%20arab%20emirates%20(UAE)%3C%2FSTRONG%3E%22%20%3CEM%3ETo%2C%3C%2FEM%3E%20%22%3CSTRONG%3EUnited%20Arab%20Emirates%20(UAE)%3C%2FSTRONG%3E%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-131205%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Excel%20keeping%20all%20cells%20uppercase%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-131205%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20use%20another%20way%2C%20it's%20forcing%20%3CSPAN%20class%3D%22%22%3Eusers%20from%20the%20beginning%20to%20enter%20uppercase%20characters%20through%20Data%20Validation%20feature.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22%22%3EJust%20follow%20these%20steps%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSPAN%20class%3D%22%22%3ESelect%20the%20entire%20column%20you%20want.%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3E%3CSPAN%20class%3D%22%22%3EGo%20to%20Data%20Ribbon%20Tab%20%26gt%3B%26gt%3B%20Data%20Tools%20Group%20%26gt%3B%26gt%3B%20Data%20Validation%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F24707iB988B10E8800B3D6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Data%20Validation.png%22%20title%3D%22Data%20Validation.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSPAN%20class%3D%22%22%3EFrom%20Data%20Validation%20dialog%20box%2C%20select%20custom%20from%20Allow%20combo%20box.%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3E%3CSPAN%20class%3D%22%22%3EIn%20the%20formula%20bar%20copy%20and%20paste%20this%20formula%3A%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CPRE%3E%3DEXACT(A1%2CUPPER(A1))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F24708i981A14EF741312A6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Data%20Validation%202.png%22%20title%3D%22Data%20Validation%202.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSPAN%20class%3D%22%22%3EPress%20OK.%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22%22%3EAfter%20that%2C%20users%20are%20not%20allowed%20to%20enter%20lowercase%20characters%20in%20column%20A.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3ENOTE%3A%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CSPAN%20class%3D%22%22%3EIf%20you%20want%20to%20apply%20this%20to%20a%20column%20other%20than%20column%20A%2C%20select%20that%20column%20entirely%2C%20and%20change%20the%20cell%20references%20in%20data%20validation%20formula.%3C%2FSPAN%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-131197%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Excel%20keeping%20all%20cells%20uppercase%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-131197%22%20slang%3D%22en-US%22%3E%3CP%3EGreg%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20possible%2C%20but%20with%20some%20lines%20of%20code!%3C%2FP%3E%0A%3CP%3EI%20have%20a%20macro%20which%20is%20fairly%20good%20to%20do%20this%20task%2C%20to%20apply%20it%20follow%20these%20steps%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EStep%201%3A%20%3C%2FSTRONG%3EPress%20Alt%2BF11%20to%20open%20the%20VBA%20editor%20as%20shown%20in%20the%20below%20screenshot%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20747px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F24704iF8232D5ED1B5134E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22VBA%20Editor.png%22%20title%3D%22VBA%20Editor.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSTRONG%3EStep%202%3A%3C%2FSTRONG%3E%20Double-click%20on%20the%20sheet%20that%20contains%20the%20column%20you%20want.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20747px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F24705iA445E24D6EFA56C4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22VBA%20Editor%202.png%22%20title%3D%22VBA%20Editor%202.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSTRONG%3EStep%203%3A%3C%2FSTRONG%3E%20Copy%20this%20code%20to%20the%20editor%2C%20then%20close%20it.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%3EPrivate%20Sub%20Worksheet_Activate()%0AOn%20Error%20Resume%20Next%0AApplication.ScreenUpdating%20%3D%20False%0A%0ADim%20cell%20As%20Range%0A%0AFor%20Each%20cell%20In%20Range(%22%24A%241%3A%22%20%26amp%3B%20Range(%22%24A%241%22).SpecialCells(xlLastCell).Address)%0A%20%20%20%20cell.Value%20%3D%20UCase(cell.Value)%0ANext%20cell%0A%0AOn%20Error%20GoTo%200%0AApplication.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CSPAN%20class%3D%22%22%3EStep%204%3A%20%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CSPAN%20class%3D%22%22%3ESave%20the%20workbook%20as%20Macro-Enabled%20Workbook%20(*.xlsm).%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22%22%3EAfter%20that%2C%20the%20macro%20will%20trigger%20each%20time%20you%20activate%20the%20worksheet.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSTRONG%3ENOTE%3A%3C%2FSTRONG%3E%20This%20macro%20is%20applied%20to%20column%20A%20in%20sheet1.%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-738640%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Excel%20keeping%20all%20cells%20uppercase%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738640%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370992%22%20target%3D%22_blank%22%3E%40SM_Talal%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20already%20replied%20to%20your%20separate%20post.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FConversion-in-PROPER-Case-except-the-word-s-written-in-CAPITAL%2Fm-p%2F737763%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FConversion-in-PROPER-Case-except-the-word-s-written-in-CAPITAL%2Fm-p%2F737763%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EHaytham%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-738654%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Excel%20keeping%20all%20cells%20uppercase%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738654%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F97727%22%20target%3D%22_blank%22%3E%40Greg%20Daughenbaugh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20very%20special%20situation%20by%20the%20way...%3C%2FP%3E%3CP%3EThe%20solutions%20provided%20by%26nbsp%3B%3Ca%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%3E%40Haytham%20Amairah%3C%2Fa%3E%20are%20great.%3C%2FP%3E%3CP%3EHowever%20you%20want%20to%20treat%20the%20different%20words%20in%20the%20same%20cell%20differently!!%3C%2FP%3E%3CP%3EI%20suggest%3A%3C%2FP%3E%3CP%3E1-%20Select%20the%20range%20(assuming%20column%20A)%20that%20you%20want%20to%20fix%20%26gt%3B%26gt%3B%20Data%20Tab%20%26gt%3B%26gt%3B%20Text%20To%20Column%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122228iA454AF50E04B4066%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Change%20Case%201.png%22%20title%3D%22Change%20Case%201.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2-%26nbsp%3B%20Data%20Tab%20%26gt%3B%26gt%3B%20Text%20To%20Columns%20(Should%20have%20blank%20columns%20to%20the%20Right)%20%26gt%3B%26gt%3B%20Check%20%22Delimiter%22%20%26gt%3B%26gt%3B%20Check%20%22Space%22%20%26gt%3B%26gt%3B%20Finish%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122229iA480E657B104880A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Change%20Case%202.png%22%20title%3D%22Change%20Case%202.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3-%26nbsp%3BIn%20Cell%20D1%20write%20the%20Formula%20that%20fixes%20the%20capitalization%20as%20desired%3C%2FP%3E%3CP%3E'%3DIF(NOT(EXACT(LOWER(A1)%2CA1))%3DFALSE%2CPROPER(A1)%2CA1)%26amp%3B%22%20%22%26amp%3B%20IF(NOT(EXACT(LOWER(B1)%2CB1))%3DFALSE%2CPROPER(B1)%2CB1)%26amp%3B%22%20%22%26amp%3B%20IF(NOT(EXACT(LOWER(C1)%2CC1))%3DFALSE%2CPROPER(C1)%2CC1)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122230iB5B2C489969795BC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Change%20Case%203.png%22%20title%3D%22Change%20Case%203.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E4-%26nbsp%3BCopy%20the%20formula%20down%3C%2FP%3E%3CP%3E5-%26nbsp%3BCopy%20All%20results%20in%20Column%20D%20%26gt%3B%26gt%3B%20Then%20Paste%20Values%20(CTRL%20%2B%20C%20%26gt%3B%26gt%3B%20ALT%20E%20S%20V%20%26gt%3B%26gt%3B%20Enter)%3C%2FP%3E%3CP%3E6-%26nbsp%3BDelete%20the%20Columns%20A%2C%20B%2C%20C%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122231i87A9500E0A7FDB81%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Change%20Case%204.png%22%20title%3D%22Change%20Case%204.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Greg Daughenbaugh
Occasional Visitor

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

5 Replies
Highlighted

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:

VBA Editor.png

 

Step 2: Double-click on the sheet that contains the column you want.

VBA Editor 2.png

 

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.

 

You can use another way, it's forcing users from the beginning to enter uppercase characters through Data Validation feature.

 

Just follow these steps:

  • Select the entire column you want.
  • Go to Data Ribbon Tab >> Data Tools Group >> Data Validation

Data Validation.png

  • From Data Validation dialog box, select custom from Allow combo box.
  • In the formula bar copy and paste this formula:
=EXACT(A1,UPPER(A1))

 

Data Validation 2.png

 

  • Press OK.

 

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.

Highlighted

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)"

 

 

 

 

 

 

 

 

@Haytham Amairah 

Highlighted
Highlighted

@Greg Daughenbaugh 

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

Change Case 1.png

 

2-  Data Tab >> Text To Columns (Should have blank columns to the Right) >> Check "Delimiter" >> Check "Space" >> Finish

Change Case 2.png

 

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)

Change Case 3.png

 

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

Change Case 4.png

Hope that helps

Nabil Mourad

 

 

 

 

Related Conversations
everything is black and white in excel
Gold4trees in Excel on
1 Replies
Deleting unwanted rows and columns
chipg900 in Excel on
3 Replies
Excel - Images -
Dmichaud65 in Excel on
1 Replies
Excel Blocked
GuerreroVan in Excel on
0 Replies
Set were Y-axis crosses X-axis
edvinana in Excel on
3 Replies
Multiple cells
Svein Tore Kristensen in Excel on
2 Replies