Capitals to lower case

  • Thread starter Thread starter Keith(Southend)
  • Start date Start date
K

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
 
Keith(Southend) said:
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
Click to expand...


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
 
Bill said:
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
Click to expand...

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

Thanks
 
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
 
Niek said:
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.
Click to expand...

Ah, that's the job :-)

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

Unless you're thinking GORD DIBBEN to Gord dibben


Gord
 
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.
|
 
=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))
or even
=LEFT(A1,1)&LOWER(RIGHT(A1,LEN(A1)-1))
Click to expand...

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))
 
Gord said:
Just curious Niek, but why not just =PROPER(A1)

Unless you're thinking GORD DIBBEN to Gord dibben


Gord
Click to expand...

=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.
 
Back
Top