| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Locating the First Comma in a Text | Excel Worksheet Functions | |||
| Capitalizing | Excel Discussion (Misc queries) | |||
| To only get all text after a comma | Excel Worksheet Functions | |||
| Text in pivot table limited to 255 characters | Excel Discussion (Misc queries) | |||
| How to convert De-limited text file to excel | Charts and Charting in Excel | |||
Linear Mode
