Capitalizing text from the left limited by a comma

[Guest]

How do I format a cell such that text left of a comma is capitalized?

example, the text in A1, "Rogers, Will", should be "ROGERS, Will"

thanks in advance

 

[meatshield]

Assuming that "Rogers, Will" is in cell A1,
the formula =UPPER(LEFT(A1,FIND(",",A1)))& RIGHT(A1,LEN(A1)-
FIND(",",A1)) will return "ROGERS, Will"
I hope that helps (it's not a pretty function)

 

[Guest]

Sorry for the length of this, no doubt someone will come up with something
insanely simple but inserted in B1 looking at text in A1 this works and it
can be dragged!!

=UPPER(IF(1>LEN(A1)-LEN(SUBSTITUTE(A1,"
","")),RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))),IF(1=1,MID(A1,1,FIND("^^",SUBSTITUTE(A1,"
","^^",1))-1),MID(A1,FIND("^^",SUBSTITUTE(A1,"
","^^",11-1))+1,FIND("^^",SUBSTITUTE(A1,"
","^^",1))-FIND("^^",SUBSTITUTE(A1,"
","^^",1-1))-1))))&IF(2>LEN(A1)-LEN(SUBSTITUTE(A1,"
","")),RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))),IF(2=1,MID(A1,1,FIND("^^",SUBSTITUTE(A1,"
","^^",1))-1),MID(A1,FIND("^^",SUBSTITUTE(A1,"
","^^",2-1))+1,FIND("^^",SUBSTITUTE(A1," ","^^",2))-FIND("^^",SUBSTITUTE(A1,"
","^^",2-1))-1)))


Mike

 

[Guest]

It's prettier than mine and saves an awful lot of typing

 

[Guest]

Thanks Meat. Thanks Mike.

Assuming that "Rogers, Will" is in cell A1,
the formula =UPPER(LEFT(A1,FIND(",",A1)))& RIGHT(A1,LEN(A1)-
FIND(",",A1)) will return "ROGERS, Will"
I hope that helps (it's not a pretty function)