Change UPPERCASE letters to lowercase based on a condition

Hi guys,

I am currently dealing with a xls file from a supplier that contains different uppercase character distribution than I need. I figured it would take a lot of time for human to manually rewrite it so if it can be done by Excel VBA, it would save me a lot of time.

What I am talking about:

1) the column "C" consists of product names

2) Now the uppercase characters are distributed this way: Great Apple Pie For Two People QCS653

3) I need it to be: Great apple pie for two people QCS653

So the VBA should be able to keep only the first letter uppercase and transform all the following characters to lowercase. However, it should also check if there is a product code (several uppercase characters followed by a number) and keep the product code uppercase. The product codes can be in this format: QCS653, T1000, 5653T

Can it be done using VBA?

Any help would be appreciated.

Thank you.

Originally Posted by kev_

Does the product code always contain a number?

Hi Kev,

yes, the product code always contain a number, sometimes it does not contain any letter though (for example 65302)

Originally Posted by kev_

Try this - puts the amended values in column D so that you can see them side by side

Please Login or Register  to view this content.

Works absolutely beutifully. Thank you!