Capitalizing text from the left limited by a comma
 

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

Capitalizing text from the left limited by a comma
 

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)

On Mar 9, 3:31 pm, Max Prophet
wrote:
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

Capitalizing text from the left limited by a comma
 

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(1LEN(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(2LEN(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 ;)

"Max Prophet" wrote:

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

Capitalizing text from the left limited by a comma
 

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

"meatshield" wrote:

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)

On Mar 9, 3:31 pm, Max Prophet
wrote:
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

Capitalizing text from the left limited by a comma
 

Thanks Meat. Thanks Mike.

"meatshield" wrote:

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)

On Mar 9, 3:31 pm, Max Prophet
wrote:
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