Formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-2816427%22%20slang%3D%22en-US%22%3EFormulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2816427%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20do%20a%20lot%20of%20name%20badges.%20People%20send%20me%20the%20list%20with%20the%20names%20to%20print%20on%20them.%20Issue%20is%20that%20some%20have%20%22First%20Name%22%20and%20others%20have%20%22First%20%26amp%3B%20Last%20Name%22.%3C%2FP%3E%3CP%3EIf%20its%20only%20one%20name%20it%20needs%20to%20be%20in%20UPPER%20case.%26nbsp%3B%20If%20two%20names%20then%20it%20is%20to%20be%20in%20Proper%20Case.%3C%2FP%3E%3CP%3EIs%20there%20a%20formula%20i%20can%20use%20so%20when%20i%20copy%20the%20list%20of%20names%20and%20paste%20them%20into%20a%20column%20in%20excel%2C%20i%20can%20have%20another%20column%20look%20in%20the%20name%20column%20and%20if%20it%20contains%201%20or%202%20names%2C%20it%20returns%20values%20in%20a%20column%20in%20either%20UPPER%20or%20Proper%20Case.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20if%20the%20name%20column%20contains%20a%20space%20and%20then%20additional%20text%20or%20something%20it%20will%20be%20considered%202%20names.%26nbsp%3B%20%26nbsp%3BThanks%20Daniel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2816427%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hi, I do a lot of name badges. People send me the list with the names to print on them. Issue is that some have "First Name" and others have "First & Last Name".

If its only one name it needs to be in UPPER case.  If two names then it is to be in Proper Case.

Is there a formula i can use so when i copy the list of names and paste them into a column in excel, i can have another column look in the name column and if it contains 1 or 2 names, it returns values in a column in either UPPER or Proper Case.

 

Maybe if the name column contains a space and then additional text or something it will be considered 2 names.   Thanks Daniel

1 Reply
Suppose the name is in cell A2:
=IF(LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))=0,UPPER(TRIM(A2)),PROPER(TRIM(A2)))
I added the TRIM functions to avoid issues with leading and trailing space characters.