How To Remove all caps in column?
In My spread sheet I have a list of names but they are all in caps... That was great for an envelope but not so good when I am going to do a letter merge. How do you remove or format the names so they are not all caps? Can you do that? Obviously I would still need the first letter to be capitalized but not the whole name. Any help would be greatly appreciated, I am stuck and do not want to type out every name .. I have excel 2002 and access 2002 and I cant seem to find out how to change this Sam -- fastblogs ------------------------------------------------------------------------ fastblogs's Profile: http://www.excelforum.com/member.php...o&userid=35118 View this thread: http://www.excelforum.com/showthread...hreadid=548727 |
How To Remove all caps in column?
You could use a VBA macro like the following:
Sub AAA() Dim Rng As Range Application.EnableEvents = False For Each Rng In Range("A1:A100") '<<< CHANGE Rng.Value = StrConv(Rng.Text, vbProperCase) Next Rng Application.EnableEvents = True End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fastblogs" wrote in message ... In My spread sheet I have a list of names but they are all in caps... That was great for an envelope but not so good when I am going to do a letter merge. How do you remove or format the names so they are not all caps? Can you do that? Obviously I would still need the first letter to be capitalized but not the whole name. Any help would be greatly appreciated, I am stuck and do not want to type out every name .. I have excel 2002 and access 2002 and I cant seem to find out how to change this Sam -- fastblogs ------------------------------------------------------------------------ fastblogs's Profile: http://www.excelforum.com/member.php...o&userid=35118 View this thread: http://www.excelforum.com/showthread...hreadid=548727 |
How To Remove all caps in column?
Other way is described in
http://www.mrexcel.com/td0109.html using the formula "proper" -- Greetings from New Zealand Bill K "Chip Pearson" wrote in message ... You could use a VBA macro like the following: Sub AAA() Dim Rng As Range Application.EnableEvents = False For Each Rng In Range("A1:A100") '<<< CHANGE Rng.Value = StrConv(Rng.Text, vbProperCase) Next Rng Application.EnableEvents = True End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fastblogs" wrote in message ... In My spread sheet I have a list of names but they are all in caps... That was great for an envelope but not so good when I am going to do a letter merge. How do you remove or format the names so they are not all caps? Can you do that? Obviously I would still need the first letter to be capitalized but not the whole name. Any help would be greatly appreciated, I am stuck and do not want to type out every name .. I have excel 2002 and access 2002 and I cant seem to find out how to change this Sam -- fastblogs ------------------------------------------------------------------------ fastblogs's Profile: http://www.excelforum.com/member.php...o&userid=35118 View this thread: http://www.excelforum.com/showthread...hreadid=548727 |
How To Remove all caps in column?
To accomplish what you want without a macro, do:
1. Insert a new column 2. Enter the formula =proper(a1) where a is your column of data 3. Fill down the formula 4. Copy the whole column 5. Paste SpecialValues 6. Delete the original column -- Regards, Fred "fastblogs" wrote in message ... In My spread sheet I have a list of names but they are all in caps... That was great for an envelope but not so good when I am going to do a letter merge. How do you remove or format the names so they are not all caps? Can you do that? Obviously I would still need the first letter to be capitalized but not the whole name. Any help would be greatly appreciated, I am stuck and do not want to type out every name .. I have excel 2002 and access 2002 and I cant seem to find out how to change this Sam -- fastblogs ------------------------------------------------------------------------ fastblogs's Profile: http://www.excelforum.com/member.php...o&userid=35118 View this thread: http://www.excelforum.com/showthread...hreadid=548727 |
How To Remove all caps in column?
Hummm.. I follow the instructions but still cant get it to work right... My list is in A1 through A312 So I moved my function column to C1 and hit Fx Then I enter in what they said =PROPER (A1) Then it says copy the forumla, Well it is not a forumula in C1, it is the name in the correct format, When I copy it to B1 to B312 Alls I get is teh name in A1 for all... Sam -- fastblogs ------------------------------------------------------------------------ fastblogs's Profile: http://www.excelforum.com/member.php...o&userid=35118 View this thread: http://www.excelforum.com/showthread...hreadid=548727 |
How To Remove all caps in column?
Wahooooooooooooooo I got it, I tried to use the function fx but could not get it to work, so I went with option 2 and set up a macro from http://www.mvps.org/dmcritchie/excel/code/proper.txt And WHAM-O It done did work there Jethro :) I highly recommend using the macro Phew...that was a tedious process figuring that out and getting it to work :eek: -- fastblogs ------------------------------------------------------------------------ fastblogs's Profile: http://www.excelforum.com/member.php...o&userid=35118 View this thread: http://www.excelforum.com/showthread...hreadid=548727 |
All times are GMT +1. The time now is 03:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com