Capital / Small letters

[Thore]

Hi all,

I have an Excel 2000 Sheet where people entered the
information "randomly" in capital and in small letters.
Now I would like to make that uniform. Is there a quick
and easy way to do so?

Thanks,
Thore

 

[Hank Scorpio]

Hi all,

I have an Excel 2000 Sheet where people entered the
information "randomly" in capital and in small letters.
Now I would like to make that uniform. Is there a quick
and easy way to do so?

There are three worksheet functions that you can use, depending on
which case you want the letters to be in; Upper(), Lower() and
Proper().

Just put the formula that you want to use in a blank column, copy it
down the length of your list of entries, and recalculate. Now copy
that column, and paste special -> Paste as values over the top of the
original entries.

 

[David McRitchie]

Hi Thore,
You really want to use a macro to change these in place, and quickly.
http://www.mvps.org/dmcritchie/excel/proper.htm

conversion to proper case, upper case, lower case. Additional
types can be found in the related area. i.e. Sentence case, small caps.

Trying to do this with worksheet formulas would take longer just to setup and copy
the formulas down with the fill-handle than the macro would, and the clean
up afterwards would take even longer.

 

[Hank Scorpio]

Hi Thore,
You really want to use a macro to change these in place, and quickly.
http://www.mvps.org/dmcritchie/excel/proper.htm

conversion to proper case, upper case, lower case. Additional
types can be found in the related area. i.e. Sentence case, small caps.

Trying to do this with worksheet formulas would take longer just to setup and copy
the formulas down with the fill-handle than the macro would, and the clean
up afterwards would take even longer.

Hi David,

I think the time issue may be stretching it just a touch; I could do
the entire formula based process described in my post (including clean
up) in well < 1 minute, especially if Upper or Lower was used. (Proper
can sometimes generate anomalies which need correcting, some of which
I note that you've addressed in your macro; though it's impossible to
predict them all.) It'd take more time than that to open the VBE,
insert a module, copy the code into it, read through what you need to
do to run it, then run it. Also use of the fill handle isn't
necessary, especially if you're proficient with the keyboard. (Put the
formula in the first row, move to the column containing the names,
[End] [Down Arrow], move back to the column containing the formulas,
[Shift]+ [End] [Up Arrow], release, [Ctrl]+[D]. Under 5 seconds, easy.
I can (and probably do) do that in my sleep, and it's way faster than
any mouse-based method. But then, I tend to have really bad dreams
anyway.)

If this is an ongoing problem, the macro solution is probably the way
to go; but for a one-off I don't think there's much difference unless
the intended output is proper, AND there are likely to be a lot of
anomalies in the content like the ones you've addressed.

JMHO.

 

[David McRitchie]

Hi Hank,

Even without the clean-up, how is that faster than...
Select the columns you want to convert
Run the macro from a shortcut (or other means)

 

[Hank Scorpio]

Hi Hank,

Even without the clean-up, how is that faster than...
Select the columns you want to convert
Run the macro from a shortcut (or other means)

Hi David,

Because as I said in the previous message... he doesn't HAVE the macro
there yet. First he has to read through your page and its
instructions, then he has to copy the relevant code, then open the
VBE, then create a module, then paste the code in, then (if he's
smart) he'll check the code over to see what it's doing[1]... and only
THEN can he run it. In the meantime the formula method's done and
dusted.

As I said, the macro approach isn't a bad one and in some
circumstances (as previously described) it has distinct advantages...
but for a quick one-off it may be like bringing in a high tech
building crew to do something that can be done by one guy and a shovel
in a couple of minutes. It really just depends on what any given
user's needs are at the time.

---------------
[1] No disrespect to your code and I'm sure it works fine... but I
always give ANYONE's code the once over before I run it to be sure
that I know what it's going to do. That includes anything that I may
have written myself a couple of years back and which turns up in a
Google search that I was doing. Especially if I have no recollection
at all of having written it. As has happened on the odd occasion...