Capitals to lower case

[Keith(Southend)]

I'm don't seem to be able to get this to work. In excel I have list of
names that are ALL in CAPITALs, and I want to quickly change them so
only the first letter is capital the rest lowercase, how can I do this?
Help takes me into 'proofing' > Auto Correct options etc..and I select
just the button that says Capitilise first letter, but how do I get this
to apply to the list?

Many thanks

 

[Bill Ridgeway]

I'm don't seem to be able to get this to work. In excel I have list of
names that are ALL in CAPITALs, and I want to quickly change them so only
the first letter is capital the rest lowercase, how can I do this? Help
takes me into 'proofing' > Auto Correct options etc..and I select just the
button that says Capitilise first letter, but how do I get this to apply
to the list?

Many thanks

Excel offers only -
=lower(a1)
=upper(a1)
=proper(a1) (which capitalises all leading letters)

The only way I have found to change to 'sentence case' is to -
copy and paste into Word
use <Format><Change Case><Sentence Case>
copy and past back into Excel

This may be a bit tedious and there may be other ways but it works!

Regards.

Bill Ridgeway
Computer Solutions

 

[Keith(Southend)]

Excel offers only -
=lower(a1)
=upper(a1)
=proper(a1) (which capitalises all leading letters)

The only way I have found to change to 'sentence case' is to -
copy and paste into Word
use <Format><Change Case><Sentence Case>
copy and past back into Excel

This may be a bit tedious and there may be other ways but it works!

Regards.

Bill Ridgeway
Computer Solutions

I'm sure I should know this, but I can't find it, but where do I find
<Format> ?

Thanks

 

[Niek Otten]

In a helper column:

=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))
or even
LEFT(A1,1)&LOWER(RIGHT(A1,LEN(A1)-1))

Copy the formulas, select the original list, Edit>Paste Special, check Values. Delete the helper column.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I'm don't seem to be able to get this to work. In excel I have list of
| names that are ALL in CAPITALs, and I want to quickly change them so
| only the first letter is capital the rest lowercase, how can I do this?
| Help takes me into 'proofing' > Auto Correct options etc..and I select
| just the button that says Capitilise first letter, but how do I get this
| to apply to the list?
|
| Many thanks
| --
| Keith (Southend)
| http://www.southendweather.net
| e-mail: kreh at southendweather dot net

 

[Keith(Southend)]

In a helper column:

=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))
or even
LEFT(A1,1)&LOWER(RIGHT(A1,LEN(A1)-1))

Copy the formulas, select the original list, Edit>Paste Special, check Values. Delete the helper column.

Ah, that's the job

Many thanks

 

[Gord Dibben]

Just curious Niek, but why not just =PROPER(A1)

Unless you're thinking GORD DIBBEN to Gord dibben


Gord

 

[Niek Otten]

Hi Gord,

<GORD DIBBEN to Gord dibben>

That's how I interpreted the OP's question!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Gord Dibben" <gorddibbATshawDOTca> wrote in message | Just curious Niek, but why not just =PROPER(A1)
|
| Unless you're thinking GORD DIBBEN to Gord dibben
|
|
| Gord
|
|
| >In a helper column:
| >
| >=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))
| >or even
| >LEFT(A1,1)&LOWER(RIGHT(A1,LEN(A1)-1))
| >
| >Copy the formulas, select the original list, Edit>Paste Special, check Values. Delete the helper column.
|

 

[Rick Rothstein]

=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))

or even
=LEFT(A1,1)&LOWER(RIGHT(A1,LEN(A1)-1))

Assuming the text is less than 1000 characters long, this is a little bit
shorter and one less function call...

=LEFT(A1)&LOWER(MID(A1,2,999))

 

[Keith(Southend)]

Just curious Niek, but why not just =PROPER(A1)

Unless you're thinking GORD DIBBEN to Gord dibben


Gord

=PROPER(A1)
Gord, thats even better for my needs.
I guess if I had been more specific I would have been pointed out this one.
Basically I have downloaded a list of Swiss weather stations, but the
list is in CAPITALS, e.g.

CIMETTA
LOCARNO-MONTI
MAGADINO-CADENAZZO
LUGANO
STABIO

What I have now got is:

Cimetta
Locarno-Monti
Magadino-Cadenazzo
Lugano
Stabio

Perfect.

Thank you all very much.

I'm still getting used to the new for me Office 2007.

Fascinating reading the posts on the group, great stuff.