Capitalizing text from the left limited by a comma

M

Max Prophet

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
 
M

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

Mike

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 ;)
 
M

Max Prophet

Thanks Meat. Thanks Mike.

meatshield said:
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)
Click to expand...
 
Top