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
SM_Talal
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
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
Thanks a lot Haytham Amairah ..

@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. :)

@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

@Jan Karel Pieterse 

 

Jan,

 

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

God Bless You.

@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.

Please post the code you have so we might advise.

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

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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies