excel - changing upper case letters into lower case letters

[BJorg]

Is there a way in excel to take a mailing list that came
to me all in upper case letters (particularly peoples
names)and convert the names from all upper case letters to
a more formal Capitalized first letter of first and last
name and then all lower case beyond that?

 

[Ken Wright]

With your data in Col A starting A1, in B1 put =PROPER(A1) and copy down. Then
select Col B, copy and then paste special as values over Col A. Now delete Col
B.

 

[Frank Kabel]

Hi
try
=PROPER(A1)
and copy this down (assuming your data is in column A)
you may copy the results and paste them as values afterwards ('Edit -
Paste Specials - Values')

 

[Bob Phillips]

I think that Ken and Frank's answer will capitalise all words,. which is not
how I read your request. This will work, on condition that all items are of
the format 'Name Name some other words'. If not, it fails.

=PROPER(LEFT(A36,FIND(" ",A36,FIND(" ",A36)+1)))&RIGHT(A36,LEN(A36)-FIND("
",A36,FIND(" ",A36)+1))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Suggest you use text to columns (on the Data menu) to split the data up. Then use the "Proper" function on the columns you want to change

When you are happy delete the columns you do not want. Use the concatenate function to put them back together

then highlight the data concatenated, click copy then use paste special "values".

 

[David McRitchie]

I would suggest using a macro to make the changes in place
rather than using a helper column. With a macro you make the
changes and it is done no more function, no extra column or
need to convert a formula to values so that you can remove
the original column. See my page
Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm
(see note at top for installing/using macros)

As a macro you can customize it to correct names that I may
have missed. For instance it definitely will correctly
correct most names beginning with Mc or Mac except
for those beginning Mack (because of too many differences),
but you can change the macro work better for your own data,
or modify the results afterwards.