Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sagit
 
Posts: n/a
Default Changing file in all upper case to upper and lower case

I have a file of names and addresses that were entered in all upper case. Is
there a fast way to change this file into upper and lower case? Thanks for
any help you can give me.
--
S Jan
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

http://www.mvps.org/dmcritchie/excel/proper.htm


--
Regards,

Peo Sjoblom

(No private emails please)


"Sagit" wrote in message
...
I have a file of names and addresses that were entered in all upper case.
Is
there a fast way to change this file into upper and lower case? Thanks
for
any help you can give me.
--
S Jan


  #3   Report Post  
Paul Sheppard
 
Posts: n/a
Default


Sagit Wrote:
I have a file of names and addresses that were entered in all upper
case. Is
there a fast way to change this file into upper and lower case? Thanks
for
any help you can give me.
--
S Jan


Hi Sagit

You could look at this earlier thread

http://www.excelforum.com/showthread...ht=change+case

or if you don't want to go down the macro route copy the data, paste
into word, make the case changes and then copy/paste back to excel


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=398021

  #4   Report Post  
Sagit
 
Posts: n/a
Default

Wow! macros look a little intimidating, but I'm going to try one. Thanks so
much for giving the code on your website. I think I will try the copy and
paste first, since I am under a deadline, but I definitely want to learn more
about using macros. Thanks a million for your help.
--
S Jan


"Paul Sheppard" wrote:


Sagit Wrote:
I have a file of names and addresses that were entered in all upper
case. Is
there a fast way to change this file into upper and lower case? Thanks
for
any help you can give me.
--
S Jan


Hi Sagit

You could look at this earlier thread

http://www.excelforum.com/showthread...ht=change+case

or if you don't want to go down the macro route copy the data, paste
into word, make the case changes and then copy/paste back to excel


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=398021


  #5   Report Post  
Sunantoro
 
Posts: n/a
Default

Basically you need to insert a temporary column and place the function:
=Proper( ) and copy it all the way down. highlight the results and click Copy
- Value - Esc. Highlight again and copy to the original name cells. Same
method for the addresses.
SUNAN

"Sagit" wrote:

I have a file of names and addresses that were entered in all upper case. Is
there a fast way to change this file into upper and lower case? Thanks for
any help you can give me.
--
S Jan



  #6   Report Post  
David McRitchie
 
Posts: n/a
Default

Hi Sagit,
What do you mean by a file? In any case you want to change the
values in place with a macro and be done with it without having to clean
up afterwards and with the ability to handle 256 columns which I hope
you would not really need but it's the principle.

To convert a selection on a worksheet see upper_case macro at
http://www.mvps.org/dmcritchie/excel/proper.htm#upper

You can select an entire column from the activecell with ctrl+spacebar,
or click on the column letter..
You can select the entire sheet with ctrl+shift+spacebar
otherwise known as ctrl+a except to those on Excel 2003
or click on th gray button between row and column headers

If by file you mean all cells in all worksheets in your workbook, see
http://www.mvps.org/dmcritchie/excel/proper.htm#kindy
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Sunantoro" wrote in message ...
Basically you need to insert a temporary column and place the function:
=Proper( ) and copy it all the way down. highlight the results and click Copy
- Value - Esc. Highlight again and copy to the original name cells. Same
method for the addresses.
SUNAN

"Sagit" wrote:

I have a file of names and addresses that were entered in all upper case. Is
there a fast way to change this file into upper and lower case? Thanks for
any help you can give me.
--
S Jan



  #7   Report Post  
Sagit
 
Posts: n/a
Default

By file, I mean a name and address list with only 8 columns: Title, First
Name, Last Name, Address, etc. I have gone to your web site and find it very
helpful. It discusses exactly what I want to do. There is a learning curve
for me, but that's why I'm in this computer business - I like to learn how to
solve problems. Thanks so much for your great suggestions.
--
S Jan


"David McRitchie" wrote:

Hi Sagit,
What do you mean by a file? In any case you want to change the
values in place with a macro and be done with it without having to clean
up afterwards and with the ability to handle 256 columns which I hope
you would not really need but it's the principle.

To convert a selection on a worksheet see upper_case macro at
http://www.mvps.org/dmcritchie/excel/proper.htm#upper

You can select an entire column from the activecell with ctrl+spacebar,
or click on the column letter..
You can select the entire sheet with ctrl+shift+spacebar
otherwise known as ctrl+a except to those on Excel 2003
or click on th gray button between row and column headers

If by file you mean all cells in all worksheets in your workbook, see
http://www.mvps.org/dmcritchie/excel/proper.htm#kindy
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Sunantoro" wrote in message ...
Basically you need to insert a temporary column and place the function:
=Proper( ) and copy it all the way down. highlight the results and click Copy
- Value - Esc. Highlight again and copy to the original name cells. Same
method for the addresses.
SUNAN

"Sagit" wrote:

I have a file of names and addresses that were entered in all upper case. Is
there a fast way to change this file into upper and lower case? Thanks for
any help you can give me.
--
S Jan




  #8   Report Post  
David McRitchie
 
Posts: n/a
Default

Thanks for feedback. Since you are working with name and address list
and have been looking at my proper.htm, you might also glance at
snakecol.htm and mailmerg.htm so you will know about them before you
need them.

Terminology is important.
Try to use worksheet or workbook depending on which you mean, instead
of ambiguous words like file or spreadsheet when referring to Excel..

For your name and address list, I find it easiest to read if the phone
number is to the left of the name(s), and address information to the
right of the name then you can see everything relative to the name.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Sagit" wrote in message ...
By file, I mean a name and address list with only 8 columns: Title, First
Name, Last Name, Address, etc. I have gone to your web site and find it very
helpful. It discusses exactly what I want to do. There is a learning curve
for me, but that's why I'm in this computer business - I like to learn how to
solve problems. Thanks so much for your great suggestions.
--
S Jan


"David McRitchie" wrote:

Hi Sagit,
What do you mean by a file? In any case you want to change the
values in place with a macro and be done with it without having to clean
up afterwards and with the ability to handle 256 columns which I hope
you would not really need but it's the principle.

To convert a selection on a worksheet see upper_case macro at
http://www.mvps.org/dmcritchie/excel/proper.htm#upper

You can select an entire column from the activecell with ctrl+spacebar,
or click on the column letter..
You can select the entire sheet with ctrl+shift+spacebar
otherwise known as ctrl+a except to those on Excel 2003
or click on th gray button between row and column headers

If by file you mean all cells in all worksheets in your workbook, see
http://www.mvps.org/dmcritchie/excel/proper.htm#kindy
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Sunantoro" wrote in message ...
Basically you need to insert a temporary column and place the function:
=Proper( ) and copy it all the way down. highlight the results and click Copy
- Value - Esc. Highlight again and copy to the original name cells. Same
method for the addresses.
SUNAN

"Sagit" wrote:

I have a file of names and addresses that were entered in all upper case. Is
there a fast way to change this file into upper and lower case? Thanks for
any help you can give me.
--
S Jan






  #9   Report Post  
Sagit
 
Posts: n/a
Default

Great idea about placement of phone number and address. I have just started
using Excel, so I appreciate the hints on terminology. I will sound more
like I know what I'm doing, now. (hopefully!)
--
S Jan


"David McRitchie" wrote:

Thanks for feedback. Since you are working with name and address list
and have been looking at my proper.htm, you might also glance at
snakecol.htm and mailmerg.htm so you will know about them before you
need them.

Terminology is important.
Try to use worksheet or workbook depending on which you mean, instead
of ambiguous words like file or spreadsheet when referring to Excel..

For your name and address list, I find it easiest to read if the phone
number is to the left of the name(s), and address information to the
right of the name then you can see everything relative to the name.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Sagit" wrote in message ...
By file, I mean a name and address list with only 8 columns: Title, First
Name, Last Name, Address, etc. I have gone to your web site and find it very
helpful. It discusses exactly what I want to do. There is a learning curve
for me, but that's why I'm in this computer business - I like to learn how to
solve problems. Thanks so much for your great suggestions.
--
S Jan


"David McRitchie" wrote:

Hi Sagit,
What do you mean by a file? In any case you want to change the
values in place with a macro and be done with it without having to clean
up afterwards and with the ability to handle 256 columns which I hope
you would not really need but it's the principle.

To convert a selection on a worksheet see upper_case macro at
http://www.mvps.org/dmcritchie/excel/proper.htm#upper

You can select an entire column from the activecell with ctrl+spacebar,
or click on the column letter..
You can select the entire sheet with ctrl+shift+spacebar
otherwise known as ctrl+a except to those on Excel 2003
or click on th gray button between row and column headers

If by file you mean all cells in all worksheets in your workbook, see
http://www.mvps.org/dmcritchie/excel/proper.htm#kindy
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Sunantoro" wrote in message ...
Basically you need to insert a temporary column and place the function:
=Proper( ) and copy it all the way down. highlight the results and click Copy
- Value - Esc. Highlight again and copy to the original name cells. Same
method for the addresses.
SUNAN

"Sagit" wrote:

I have a file of names and addresses that were entered in all upper case. Is
there a fast way to change this file into upper and lower case? Thanks for
any help you can give me.
--
S Jan






  #10   Report Post  
Sagit
 
Posts: n/a
Default

I could successfully execute your instructions through the =PROPER (B2) step,
but after I highlight and copy the results (Mr. And Mrs.) I don't understand
how to do the VALUE, Esc. I've gone to Help, and it says VALUE is for
numbers and gives an example that doesn't apply to me. Could you please
explain it in a little more detail. Thanks.
--
S Jan


"Sunantoro" wrote:

Basically you need to insert a temporary column and place the function:
=Proper( ) and copy it all the way down. highlight the results and click Copy
- Value - Esc. Highlight again and copy to the original name cells. Same
method for the addresses.
SUNAN

"Sagit" wrote:

I have a file of names and addresses that were entered in all upper case. Is
there a fast way to change this file into upper and lower case? Thanks for
any help you can give me.
--
S Jan



  #11   Report Post  
David McRitchie
 
Posts: n/a
Default

Eliminate this list dance is why it is so much easier using a macro.

You have a column perhaps starting in A1
You placed a formula dependent on column A into cell B1:
=PROPER(A1)

You would like to climate column A, but can't because column B
is dependent on Column A.

Select column B, then copy (ctrl+C)
With B still selected
Edit, Paste Special, Values, [Ok] -- not Escape

Values refers to text and characters, in this context.
You will see that the formulas have been converted to values
and can then remove column A.

Use of a macro is so much faster.


HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Sagit" wrote in message ...
I could successfully execute your instructions through the =PROPER (B2) step,
but after I highlight and copy the results (Mr. And Mrs.) I don't understand
how to do the VALUE, Esc. I've gone to Help, and it says VALUE is for
numbers and gives an example that doesn't apply to me. Could you please
explain it in a little more detail. Thanks.
--
S Jan


"Sunantoro" wrote:

Basically you need to insert a temporary column and place the function:
=Proper( ) and copy it all the way down. highlight the results and click Copy
- Value - Esc. Highlight again and copy to the original name cells. Same
method for the addresses.
SUNAN

"Sagit" wrote:

I have a file of names and addresses that were entered in all upper case. Is
there a fast way to change this file into upper and lower case? Thanks for
any help you can give me.
--
S Jan



  #12   Report Post  
Sagit
 
Posts: n/a
Default

OK, I gotcha now. The mistake I made was in doing a Paste, instead of a
Paste Special. You are right, this is tedious. But I have accomplished what
I needed. Now, I will learn how to do it with a macro. Thank you so very
much for your extra tutoring. You are great.
--
S Jan


"David McRitchie" wrote:

Eliminate this list dance is why it is so much easier using a macro.

You have a column perhaps starting in A1
You placed a formula dependent on column A into cell B1:
=PROPER(A1)

You would like to climate column A, but can't because column B
is dependent on Column A.

Select column B, then copy (ctrl+C)
With B still selected
Edit, Paste Special, Values, [Ok] -- not Escape

Values refers to text and characters, in this context.
You will see that the formulas have been converted to values
and can then remove column A.

Use of a macro is so much faster.


HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Sagit" wrote in message ...
I could successfully execute your instructions through the =PROPER (B2) step,
but after I highlight and copy the results (Mr. And Mrs.) I don't understand
how to do the VALUE, Esc. I've gone to Help, and it says VALUE is for
numbers and gives an example that doesn't apply to me. Could you please
explain it in a little more detail. Thanks.
--
S Jan


"Sunantoro" wrote:

Basically you need to insert a temporary column and place the function:
=Proper( ) and copy it all the way down. highlight the results and click Copy
- Value - Esc. Highlight again and copy to the original name cells. Same
method for the addresses.
SUNAN

"Sagit" wrote:

I have a file of names and addresses that were entered in all upper case. Is
there a fast way to change this file into upper and lower case? Thanks for
any help you can give me.
--
S Jan




  #13   Report Post  
Sagit
 
Posts: n/a
Default

My husband told me that before I can use a macro, I need to have a VB
compiler. He said they are expensive. Not that I don't have confidence in
my husband's knowledge, but could you please confirm that and where is the
best place to buy one?
--
S Jan


"David McRitchie" wrote:

Eliminate this list dance is why it is so much easier using a macro.

You have a column perhaps starting in A1
You placed a formula dependent on column A into cell B1:
=PROPER(A1)

You would like to climate column A, but can't because column B
is dependent on Column A.

Select column B, then copy (ctrl+C)
With B still selected
Edit, Paste Special, Values, [Ok] -- not Escape

Values refers to text and characters, in this context.
You will see that the formulas have been converted to values
and can then remove column A.

Use of a macro is so much faster.


HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Sagit" wrote in message ...
I could successfully execute your instructions through the =PROPER (B2) step,
but after I highlight and copy the results (Mr. And Mrs.) I don't understand
how to do the VALUE, Esc. I've gone to Help, and it says VALUE is for
numbers and gives an example that doesn't apply to me. Could you please
explain it in a little more detail. Thanks.
--
S Jan


"Sunantoro" wrote:

Basically you need to insert a temporary column and place the function:
=Proper( ) and copy it all the way down. highlight the results and click Copy
- Value - Esc. Highlight again and copy to the original name cells. Same
method for the addresses.
SUNAN

"Sagit" wrote:

I have a file of names and addresses that were entered in all upper case. Is
there a fast way to change this file into upper and lower case? Thanks for
any help you can give me.
--
S Jan




  #14   Report Post  
David McRitchie
 
Posts: n/a
Default

You don't need to purchase VB. Excel comes with VBA which is
all you need for macros. Before you can use macros you must
set security level within Excel to Medium.
tools, macros, security, medium, [OK]

To install macros you find in newsgroups or websites, see
http://www.mvps.org/dmcritchie/excel/getstarted.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Sagit" wrote in message ...
My husband told me that before I can use a macro, I need to have a VB
compiler. He said they are expensive. Not that I don't have confidence in
my husband's knowledge, but could you please confirm that and where is the
best place to buy one?
--
S Jan


"David McRitchie" wrote:

Eliminate this list dance is why it is so much easier using a macro.

You have a column perhaps starting in A1
You placed a formula dependent on column A into cell B1:
=PROPER(A1)

You would like to climate column A, but can't because column B
is dependent on Column A.

Select column B, then copy (ctrl+C)
With B still selected
Edit, Paste Special, Values, [Ok] -- not Escape

Values refers to text and characters, in this context.
You will see that the formulas have been converted to values
and can then remove column A.

Use of a macro is so much faster.


HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Sagit" wrote in message ...
I could successfully execute your instructions through the =PROPER (B2) step,
but after I highlight and copy the results (Mr. And Mrs.) I don't understand
how to do the VALUE, Esc. I've gone to Help, and it says VALUE is for
numbers and gives an example that doesn't apply to me. Could you please
explain it in a little more detail. Thanks.
--
S Jan


"Sunantoro" wrote:

Basically you need to insert a temporary column and place the function:
=Proper( ) and copy it all the way down. highlight the results and click Copy
- Value - Esc. Highlight again and copy to the original name cells. Same
method for the addresses.
SUNAN

"Sagit" wrote:

I have a file of names and addresses that were entered in all upper case. Is
there a fast way to change this file into upper and lower case? Thanks for
any help you can give me.
--
S Jan







  #15   Report Post  
Sagit
 
Posts: n/a
Default

My husband and I both learned something, thanks to you. I will check it out
and thanks once again. I love this newsgroup - everyone is not only very
knowledgeable, but so very nice and helpful, you especially.
--
S Jan


"David McRitchie" wrote:

You don't need to purchase VB. Excel comes with VBA which is
all you need for macros. Before you can use macros you must
set security level within Excel to Medium.
tools, macros, security, medium, [OK]

To install macros you find in newsgroups or websites, see
http://www.mvps.org/dmcritchie/excel/getstarted.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Sagit" wrote in message ...
My husband told me that before I can use a macro, I need to have a VB
compiler. He said they are expensive. Not that I don't have confidence in
my husband's knowledge, but could you please confirm that and where is the
best place to buy one?
--
S Jan


"David McRitchie" wrote:

Eliminate this list dance is why it is so much easier using a macro.

You have a column perhaps starting in A1
You placed a formula dependent on column A into cell B1:
=PROPER(A1)

You would like to climate column A, but can't because column B
is dependent on Column A.

Select column B, then copy (ctrl+C)
With B still selected
Edit, Paste Special, Values, [Ok] -- not Escape

Values refers to text and characters, in this context.
You will see that the formulas have been converted to values
and can then remove column A.

Use of a macro is so much faster.


HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Sagit" wrote in message ...
I could successfully execute your instructions through the =PROPER (B2) step,
but after I highlight and copy the results (Mr. And Mrs.) I don't understand
how to do the VALUE, Esc. I've gone to Help, and it says VALUE is for
numbers and gives an example that doesn't apply to me. Could you please
explain it in a little more detail. Thanks.
--
S Jan


"Sunantoro" wrote:

Basically you need to insert a temporary column and place the function:
=Proper( ) and copy it all the way down. highlight the results and click Copy
- Value - Esc. Highlight again and copy to the original name cells. Same
method for the addresses.
SUNAN

"Sagit" wrote:

I have a file of names and addresses that were entered in all upper case. Is
there a fast way to change this file into upper and lower case? Thanks for
any help you can give me.
--
S Jan









  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 49
Default Changing file in all upper case to upper and lower case

Copy the following macro:

Sub Change_Case()
Dim ocell As Range
Dim Ans As String
Ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")
If Ans = "" Then Exit Sub
For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
Select Case UCase(Ans)
Case "L": ocell = LCase(ocell.Text)
Case "U": ocell = UCase(ocell.Text)
Case "S": ocell = UCase(Left(ocell.Text, 1)) & _
LCase(Right(ocell.Text, Len(ocell.Text) - 1))
Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
End Select
Next

End Sub


"Sagit" wrote:

I have a file of names and addresses that were entered in all upper case. Is
there a fast way to change this file into upper and lower case? Thanks for
any help you can give me.
--
S Jan

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I change a column in Excel from upper case to lower case? Debbie Kennedy Excel Worksheet Functions 3 May 2nd 05 06:57 PM
TURN LOWER CASE TO UPPER CASE IN EXCEL? TommyG Excel Worksheet Functions 2 March 31st 05 01:54 AM
How do I convert all upper case excel sheet into upper and lower . DebDay Excel Discussion (Misc queries) 1 March 9th 05 09:31 PM
changing lower case to upper case T. Campbell Excel Discussion (Misc queries) 1 December 8th 04 06:37 PM
How do I change existing text from lower case to upper case CT Cameron Excel Discussion (Misc queries) 2 November 30th 04 02:07 AM


All times are GMT +1. The time now is 11:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"