Need to Change Case from Mixed to Caps

  • Thread starter Thread starter Kathy
  • Start date Start date
K

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.
 
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.
 
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
 
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
 
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?
 
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?
 
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.
 
PERFECT! Thank you!

Roger Govier said:
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

.
Click to expand...
 
Back
Top