Need to Change Case from Mixed to Caps

[Kathy]

Please help! I have a number of imported worksheets with some fields
formatted in mixed case and some in UPPER case. I want all data to look
consistent.

Is there a way that I can quickly turn all of the mixed case to upper case?
Word has a button to do this but I don't see one in Excel.

Thanks in advance.

 

[Mike H]

Hi,

have a look at the UPPER function in XLHelp
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Pritesh]

You can apply "Upper" formula, even better would be to use "Proper" formula,
which capitalizes only first letter of each word from the reference data/cell.

It works like;
=upper(a1) and
=proper(a1)

Regards,
Pritesh

 

[ozgrid.com]

Run this macro;

Sub UPPERCaseAll()
Dim lchr As Long

For lchr = 97 To 127
ActiveSheet.UsedRange.Replace Chr(lchr), UCase(Chr(lchr)), xlPart
Next lchr
End Sub

 

[Kathy]

Thank you! This works but now I need to get rid of the column with the
improperly formatted case (so that there are not two duplicate columns of
text). How can I do that? Can I turn the new column with the "Upper" and
"Proper" formulas into text?

 

[Kathy]

Thank you! This works but now I need to get rid of the column with the
improperly formatted case (so that there are not two duplicate columns of
text). How can I do that? Can I turn the new column with the "Upper" and
"Proper" formulas into text?

 

[Roger Govier]

Hi Kathy

Copy your new column which has the formulae>Paste Special>Values
That will "fix" the formulae to their resulting text appearance.
You can then delete the original column.

 

[Kathy]

PERFECT! Thank you!

Hi Kathy

Copy your new column which has the formulae>Paste Special>Values
That will "fix" the formulae to their resulting text appearance.
You can then delete the original column.
--
Regards
Roger Govier

.