How to Uncapitalize large data field

[pschiess]

I was given a large amount of data that was mostly entered with cap locks on so now it is all capitalized. How do I go about changing it back to standard formating? I tried the trick from word of Shift+f3 but it did not work. there must be an easy way but i can not find it.

[VBA Noob]

Hi,

With a helper column. Say data in Col A enter this in B1

=Proper(A1)

Drag down then paste special values to get rid of the formula's

Or using VBA...see link

http://www.ozgrid.com/VBA/change-case-text.htm

HTH

VBA Noob

[jasoncw]

There is no quick and dirty way to change all upper case to lower case. You can do this one of two ways.

1. With a formula for each cell:

Please Login or Register  to view this content.

Copy the formula down, then copy all cells with the formula then select Edit > Paste Special > values. If you have a very large range that is not contiguous, this might take a while.

2. With VBA code:

Please Login or Register  to view this content.

This will automatically convert all cells in the used range to lower case.

HTH

Jason

[carsto]

in Excel you have to add a column which returns the original data in lower or title case

when the data is in A1

to get lower case in B1 put =LOWER(A1)
to get Title Case in B1 put =PROPER(A1)

if you have multiple columns, or you want Sentence Case
you can export it into a table in Word, change the case and bring it back into Excel.

[pschiess]

It worked perfectly (with a little tweaking of a few entries but that saved me a ton of work. Thanks for your help!!

[VBA Noob]

Glad you got a solution

VBA Noob