SOLVED
Home

Conversion in PROPER Case except the word(s) written in CAPITAL Letter.

%3CLINGO-SUB%20id%3D%22lingo-sub-737763%22%20slang%3D%22en-US%22%3EConversion%20in%20PROPER%20Case%26nbsp%3Bexcept%20the%20word(s)%20written%20in%20CAPITAL%20Letter.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-737763%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%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%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%22%3CSTRONG%3EI%20love%20APPLE%3C%2FSTRONG%3E%22%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3ETo%2C%3C%2FEM%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%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%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3ETo%2C%3C%2FEM%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%22%3CSTRONG%3EUnited%20Arab%20Emirates%20(UAE)%3C%2FSTRONG%3E%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-737763%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-738590%22%20slang%3D%22en-US%22%3ERe%3A%20Conversion%20in%20PROPER%20Case%26nbsp%3Bexcept%20the%20word(s)%20written%20in%20CAPITAL%20Letter.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738590%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%20This%20User%20defined%20function%20(in%20a%20normal%20module%20in%20VBA)%20seems%20to%20do%20it%3A%3C%2FP%3E%0A%3CPRE%3EOption%20Explicit%0A%0AFunction%20MyProper(Source%20As%20Variant)%20As%20String%0A%20%20%20%20Dim%20Data%20As%20Variant%0A%20%20%20%20Dim%20lWd%20As%20Long%0A%20%20%20%20Dim%20Words%20As%20Variant%0A%20%20%20%20If%20TypeName(Source)%20%3D%20%22Range%22%20Then%0A%20%20%20%20%20%20%20%20Data%20%3D%20Source.Value%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20Data%20%3D%20Source%0A%20%20%20%20End%20If%0A%20%20%20%20Words%20%3D%20Split(Data%2C%20%22%20%22)%0A%20%20%20%20For%20lWd%20%3D%20LBound(Words%2C%201)%20To%20UBound(Words%2C%201)%0A%20%20%20%20%20%20%20%20If%20Words(lWd)%20%26lt%3B%26gt%3B%20UCase(Words(lWd))%20Then%0A%20%20%20%20%20%20%20%20Words(lWd)%20%3D%20Application.Proper(Words(lWd))%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%0A%20%20%20%20MyProper%20%3D%20Trim(Join(Words%2C%20%22%20%22))%0AEnd%20Function%0A%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-738637%22%20slang%3D%22en-US%22%3ERe%3A%20Conversion%20in%20PROPER%20Case%26nbsp%3Bexcept%20the%20word(s)%20written%20in%20CAPITAL%20Letter.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738637%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'll%20show%20you%20the%20easiest%20way%20I%20found%20to%20do%20so!%3C%2FP%3E%3CP%3EPlease%20check%20out%20this%20video%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2F1drv.ms%2Fv%2Fs!ArtU2vr1IWYYhLsK1YfN0Kdj2f-CQg%3Fe%3DQ40WSS%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2F1drv.ms%2Fv%2Fs!ArtU2vr1IWYYhLsK1YfN0Kdj2f-CQg%3Fe%3DQ40WSS%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-738962%22%20slang%3D%22en-US%22%3ERe%3A%20Conversion%20in%20PROPER%20Case%26nbsp%3Bexcept%20the%20word(s)%20written%20in%20CAPITAL%20Letter.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738962%22%20slang%3D%22en-US%22%3EThanks%20a%20lot%20Haytham%20Amairah%20..%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-738968%22%20slang%3D%22en-US%22%3ERe%3A%20Conversion%20in%20PROPER%20Case%26nbsp%3Bexcept%20the%20word(s)%20written%20in%20CAPITAL%20Letter.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738968%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Jan%2C%3C%2FP%3E%3CP%3EThis%20really%20helped%20me%20a%20lot%2C%20now%20I%20want%20to%20increase%20the%20difficulty%20level%20little%20bit.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CU%3EFor%20E%3Ag%2C%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CEM%3EFrom%3A%3C%2FEM%3E%3C%2FSTRONG%3E%20%22I%20got%20%3CSTRONG%3E4th%3C%2FSTRONG%3E%20position%20in%20grade%20EIGHT%22%20%3CSTRONG%3E%3CEM%3ETo%3A%20%3C%2FEM%3E%3C%2FSTRONG%3E%22I%20Got%20%3CSTRONG%3E4th%3C%2FSTRONG%3E%20Position%20In%20Grade%20EIGHT%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhile%20applying%20PROPER%20%22%3CSTRONG%3E4th%22%3C%2FSTRONG%3E%20will%20become%20%22%3CSTRONG%3E4Th%22%20%3C%2FSTRONG%3Ewhich%20in%20incorrect.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20we%20have%20to%20%3CSTRONG%3E%3CEM%3Erestrict%3C%2FEM%3E%3C%2FSTRONG%3E%26nbsp%3Ba%20%3CSTRONG%3E%3CEM%3Ecapital%20letter%3C%2FEM%3E%20%3C%2FSTRONG%3Eafter%20a%20%3CSTRONG%3En%3CEM%3Eumber%3C%2FEM%3E%3C%2FSTRONG%3E%2C%20Like%201st%2C%202nd%2C%203rd%2C4th%26nbsp%3Betc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20once%20again.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-739024%22%20slang%3D%22en-US%22%3ERe%3A%20Conversion%20in%20PROPER%20Case%26nbsp%3Bexcept%20the%20word(s)%20written%20in%20CAPITAL%20Letter.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-739024%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%20In%20that%20case%2C%20update%20the%20function%20to%3A%3C%2FP%3E%0A%3CPRE%3EFunction%20MyProper(Source%20As%20Variant)%20As%20String%0A%20%20%20%20Dim%20Data%20As%20Variant%0A%20%20%20%20Dim%20lWd%20As%20Long%0A%20%20%20%20Dim%20Words%20As%20Variant%0A%20%20%20%20If%20TypeName(Source)%20%3D%20%22Range%22%20Then%0A%20%20%20%20%20%20%20%20Data%20%3D%20Source.Value%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20Data%20%3D%20Source%0A%20%20%20%20End%20If%0A%20%20%20%20Words%20%3D%20Split(Data%2C%20%22%20%22)%0A%20%20%20%20For%20lWd%20%3D%20LBound(Words%2C%201)%20To%20UBound(Words%2C%201)%0A%20%20%20%20%20%20%20%20If%20Words(lWd)%20%26lt%3B%26gt%3B%20UCase(Words(lWd))%20And%20Not%20Isnumeric(Left(Words(lWd)%2C1))%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Words(lWd)%20%3D%20Application.Proper(Words(lWd))%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%0A%20%20%20%20MyProper%20%3D%20Trim(Join(Words%2C%20%22%20%22))%0AEnd%20Function%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-739026%22%20slang%3D%22en-US%22%3ERe%3A%20Conversion%20in%20PROPER%20Case%26nbsp%3Bexcept%20the%20word(s)%20written%20in%20CAPITAL%20Letter.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-739026%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJan%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20..%20That's%26nbsp%3Bvery%20great%20..%20I%20cannot%20explain..%3C%2FP%3E%3CP%3EGod%20Bless%20You.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-902401%22%20slang%3D%22en-US%22%3ERe%3A%20Conversion%20in%20PROPER%20Case%26nbsp%3Bexcept%20the%20word(s)%20written%20in%20CAPITAL%20Letter.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-902401%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3BI%20may%20have%20a%20problem%20to%20up%20the%20ante%20a%20bit%20more.%26nbsp%3B%20I've%20been%20looking%20through%20the%20forums%20for%20a%20function%20that%20helps%20me%20turn%20all%20caps%20text%20input%20into%20a%20Proper%20string%2C%26nbsp%3B%3CEM%3Ewith%20some%20exceptions%3C%2FEM%3E.%26nbsp%3B%20If%20one%20of%20the%20words%20is%20a%20specific%20abbreviation%20(%22CR%22%20or%20%22SR%22%20or%20%22IH%22%20or%20%22US%22)%2C%20then%20I%20need%20that%20word%20to%20stay%20all%20caps.%26nbsp%3B%20If%20a%20word%20begins%20with%20a%20numeric%20(1TH%2C%202ND%2C%203RD%2C%204TH%2C%20etc.)%2C%20then%20I%20need%20it%20to%20switch%20to%20lower%20case.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20examples%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3CSTRONG%3EFrom%3A%26nbsp%3B%3C%2FSTRONG%3E%22EAST%202ND%20STREET%22%26nbsp%3B%26nbsp%3B%3CSTRONG%3ETo%3A%3C%2FSTRONG%3E%26nbsp%3B%22East%202nd%20Street%22%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3CSTRONG%3EFrom%3A%26nbsp%3B%3C%2FSTRONG%3E%22IH%2059%20SOUNDBOUND%22%26nbsp%3B%26nbsp%3B%3CSTRONG%3ETo%3A%3C%2FSTRONG%3E%26nbsp%3B%22IH%2059%20Southbound%22%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3CSTRONG%3EFrom%3A%26nbsp%3B%3C%2FSTRONG%3E%22BROWN%20CREEK%20ROAD%22%26nbsp%3B%26nbsp%3B%3CSTRONG%3ETo%3A%3C%2FSTRONG%3E%26nbsp%3B%22Brown%20Creek%20Road%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20feel%20like%20I%20know%20what%20needs%20to%20be%20done%2C%20but%20I%20don't%20think%20I'm%20getting%20the%20syntax%20right.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-903802%22%20slang%3D%22en-US%22%3ERe%3A%20Conversion%20in%20PROPER%20Case%26nbsp%3Bexcept%20the%20word(s)%20written%20in%20CAPITAL%20Letter.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-903802%22%20slang%3D%22en-US%22%3EPlease%20post%20the%20code%20you%20have%20so%20we%20might%20advise.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-903824%22%20slang%3D%22en-US%22%3ERe%3A%20Conversion%20in%20PROPER%20Case%26nbsp%3Bexcept%20the%20word(s)%20written%20in%20CAPITAL%20Letter.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-903824%22%20slang%3D%22en-US%22%3E%3CP%3EI%20think%20I%26nbsp%3B%3CEM%3Ejust%3C%2FEM%3E%20came%20up%20with%20something%20workable%20(albeit%20very%20inefficient)...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFunction%20MyProper(Source%20As%20Variant)%20As%20String%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20Data%20As%20Variant%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20lWd%20As%20Long%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20Words%20As%20Variant%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20TypeName(Source)%20%3D%20%22Range%22%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Data%20%3D%20Source.Value%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Else%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Data%20%3D%20Source%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20If%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Words%20%3D%20Split(Data%2C%20%22%20%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20For%20lWd%20%3D%20LBound(Words%2C%201)%20To%20UBound(Words%2C%201)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20IsNumeric(Left(Words(lWd)%2C%201))%20And%20Right(Words(lWd)%2C%202)%20%3D%20%22ST%22%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Words(lWd)%20%3D%20LCase(Words(lWd))%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ElseIf%20IsNumeric(Left(Words(lWd)%2C%201))%20And%20Right(Words(lWd)%2C%202)%20%3D%20%22ND%22%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Words(lWd)%20%3D%20LCase(Words(lWd))%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ElseIf%20IsNumeric(Left(Words(lWd)%2C%201))%20And%20Right(Words(lWd)%2C%202)%20%3D%20%22RD%22%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Words(lWd)%20%3D%20LCase(Words(lWd))%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ElseIf%20IsNumeric(Left(Words(lWd)%2C%201))%20And%20Right(Words(lWd)%2C%202)%20%3D%20%22TH%22%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Words(lWd)%20%3D%20LCase(Words(lWd))%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ElseIf%20Words(lWd)%20%3D%20%22CR%22%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Words(lWd)%20%3D%20UCase(Words(lWd))%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ElseIf%20Words(lWd)%20%3D%20%22US%22%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Words(lWd)%20%3D%20UCase(Words(lWd))%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ElseIf%20Words(lWd)%20%3D%20%22SR%22%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Words(lWd)%20%3D%20UCase(Words(lWd))%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ElseIf%20Words(lWd)%20%3D%20%22IH%22%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Words(lWd)%20%3D%20UCase(Words(lWd))%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Else%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Words(lWd)%20%3D%20Application.Proper(Words(lWd))%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20If%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Next%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20MyProper%20%3D%20Trim(Join(Words%2C%20%22%20%22))%3C%2FP%3E%3CP%3EEnd%20Function%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-904047%22%20slang%3D%22en-US%22%3ERe%3A%20Conversion%20in%20PROPER%20Case%26nbsp%3Bexcept%20the%20word(s)%20written%20in%20CAPITAL%20Letter.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-904047%22%20slang%3D%22en-US%22%3ENot%20bad%20at%20all.%20Could%20be%20slightly%20improved%20by%20moving%20the%20isnumeric%20test%20into%20a%20separate%20if%20statement%20under%20which%20you%20nest%20all%20related%20other%20tests.%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi,

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

10 Replies
Highlighted
Solution

@SM_Talal This User defined function (in a normal module in VBA) seems to do it:

Option Explicit

Function MyProper(Source As Variant) As String
    Dim Data As Variant
    Dim lWd As Long
    Dim Words As Variant
    If TypeName(Source) = "Range" Then
        Data = Source.Value
    Else
        Data = Source
    End If
    Words = Split(Data, " ")
    For lWd = LBound(Words, 1) To UBound(Words, 1)
        If Words(lWd) <> UCase(Words(lWd)) Then
        Words(lWd) = Application.Proper(Words(lWd))
        End If
    Next
    MyProper = Trim(Join(Words, " "))
End Function
Highlighted

@SM_Talal

 

Hi,

 

I'll show you the easiest way I found to do so!

Please check out this video:

https://1drv.ms/v/s!ArtU2vr1IWYYhLsK1YfN0Kdj2f-CQg?e=Q40WSS

 

Hope that helps

 

Highlighted
Thanks a lot Haytham Amairah ..
Highlighted

@Jan Karel Pieterse 

Hi Jan,

This really helped me a lot, now I want to increase the difficulty level little bit.

For E:g,

From: "I got 4th position in grade EIGHT" To: "I Got 4th Position In Grade EIGHT"

 

While applying PROPER "4th" will become "4Th" which in incorrect.

 

Here we have to restrict a capital letter after a number, Like 1st, 2nd, 3rd,4th etc.

 

Thank you once again. :)

Highlighted

@SM_Talal In that case, update the function to:

Function MyProper(Source As Variant) As String
    Dim Data As Variant
    Dim lWd As Long
    Dim Words As Variant
    If TypeName(Source) = "Range" Then
        Data = Source.Value
    Else
        Data = Source
    End If
    Words = Split(Data, " ")
    For lWd = LBound(Words, 1) To UBound(Words, 1)
        If Words(lWd) <> UCase(Words(lWd)) And Not Isnumeric(Left(Words(lWd),1)) Then
            Words(lWd) = Application.Proper(Words(lWd))
        End If
    Next
    MyProper = Trim(Join(Words, " "))
End Function
Highlighted

@Jan Karel Pieterse 

 

Jan,

 

Thank you very much .. That's very great .. I cannot explain..

God Bless You.

Highlighted

@Jan Karel Pieterse I may have a problem to up the ante a bit more.  I've been looking through the forums for a function that helps me turn all caps text input into a Proper string, with some exceptions.  If one of the words is a specific abbreviation ("CR" or "SR" or "IH" or "US"), then I need that word to stay all caps.  If a word begins with a numeric (1TH, 2ND, 3RD, 4TH, etc.), then I need it to switch to lower case.

 

Some examples:

  From: "EAST 2ND STREET"  To: "East 2nd Street"

  From: "IH 59 SOUNDBOUND"  To: "IH 59 Southbound" 

  From: "BROWN CREEK ROAD"  To: "Brown Creek Road"

 

I feel like I know what needs to be done, but I don't think I'm getting the syntax right.

Highlighted
Please post the code you have so we might advise.
Highlighted

I think I just came up with something workable (albeit very inefficient)...

 

Function MyProper(Source As Variant) As String

    Dim Data As Variant

    Dim lWd As Long

    Dim Words As Variant

    If TypeName(Source) = "Range" Then

        Data = Source.Value

    Else

        Data = Source

    End If

    Words = Split(Data, " ")

    For lWd = LBound(Words, 1) To UBound(Words, 1)

        If IsNumeric(Left(Words(lWd), 1)) And Right(Words(lWd), 2) = "ST" Then

            Words(lWd) = LCase(Words(lWd))

        ElseIf IsNumeric(Left(Words(lWd), 1)) And Right(Words(lWd), 2) = "ND" Then

            Words(lWd) = LCase(Words(lWd))

        ElseIf IsNumeric(Left(Words(lWd), 1)) And Right(Words(lWd), 2) = "RD" Then

            Words(lWd) = LCase(Words(lWd))

        ElseIf IsNumeric(Left(Words(lWd), 1)) And Right(Words(lWd), 2) = "TH" Then

            Words(lWd) = LCase(Words(lWd))

        ElseIf Words(lWd) = "CR" Then

            Words(lWd) = UCase(Words(lWd))

        ElseIf Words(lWd) = "US" Then

            Words(lWd) = UCase(Words(lWd))

        ElseIf Words(lWd) = "SR" Then

            Words(lWd) = UCase(Words(lWd))

        ElseIf Words(lWd) = "IH" Then

            Words(lWd) = UCase(Words(lWd))

        Else

            Words(lWd) = Application.Proper(Words(lWd))

        End If

    Next

    MyProper = Trim(Join(Words, " "))

End Function

Highlighted
Not bad at all. Could be slightly improved by moving the isnumeric test into a separate if statement under which you nest all related other tests.
Related Conversations
export doc to pdf on mac
kyrajayne in Word on
0 Replies
Autosave Not Working
Promise46 in Word on
1 Replies
Word 365 svg import problem
Goupil35000 in Office 365 on
0 Replies
Page numbers won't start at 1
jamieschwenger in Office for Mac on
5 Replies
Deleting Sections in Word for the Mac
Bob_Irvine in Office for Mac on
0 Replies