Making caps lowercase?

[james_tang]

I have a column of name/state names in all caps, for example:

EXAMPLE COMPLEX, NJ

Is there any way to make all of these lowercase into something like this:

Example Complex, NJ

but retain the caps for the state names?

Thanks.

[SamuelT]

Hi,

Presuming your capitalized words are in A1, this should work:

Please Login or Register  to view this content.

HTH,

SamuelT

[NBVC]

Try:

=PROPER(LEFT(A1,FIND(",",A1)-1))&MID(A1,FIND(",",A1),LEN(A1))

where A1 houses original string.

[james_tang]

Awesome, thanks so much that works perfectly.

The only thing is, there are some complex names that don't have a state attached at the end.

Right now the function returns !#VALUE if that's the case. Is there a way to get around this so:

SAN FRANCISCO COMPLEX

turns into

San Francisco Complex

with one formula?

Thanks.

[NBVC]

Try:

Please Login or Register  to view this content.