PDA

View Full Version : Solved: CHANGE FROM ALL UPPER CASE



GaryB
09-21-2007, 09:11 AM
Hi,

I have a database that is to be used for a mailing list and it is all in uppercase. I need to do a global change to Upper and lower case. Anyone have an idea on how I can do this.

Thanks

Gary

Bob Phillips
09-21-2007, 09:43 AM
For Eachcell In Activesheet.UsedRange
cell.Value = UCase(cell.Value)
Next cell

Norie
09-21-2007, 09:58 AM
Gary

You don't actually say how you want to change the case but I think you might want to take a look at StrConv with the vbProperCase argument.

GaryB
09-21-2007, 10:21 AM
My aplologies, I wasn't clear enough. I do want to change Proper Case but don't know enough VB to do it myself. I have a list of 800 names and address that are all capitalized and I need to change it to just the first letter of the word capitalized. I do have a way of doing this by importing it into Indesign as a comma, text delimited file, making the change there and importing it back into Excel. Which I have done. I was looking for a way to do it in Excel and eliminate about 4 steps to the process.

Thanks,

Gary

RonMcK
09-21-2007, 10:23 AM
Gary,

Combining XID's and Norie's suggestions gives

For Eachcell In Activesheet.UsedRange
cell.Value = StrConv(cell.Value, 2) ' where 3 = vbProperCase
Next cell

Works very nicely on my very small sample. Areas that may be problems: hyphenated names (StrConv doesn't see "-" as a word separator) and Canadian and UK postal codes (mixed alphanumerics).

Cheers!:thumb

RonMcK
09-21-2007, 10:24 AM
Gary,

Combining XID's and Norie's suggestions gives

For Eachcell In Activesheet.UsedRange
cell.Value = StrConv(cell.Value, 2) ' where 3 = vbProperCase
Next cell
Works very nicely on my very small sample. Areas that may be problems: hyphenated names (StrConv doesn't see "-" as a word separator) and Canadian and UK postal codes (mixed alphanumerics).

Cheers!:thumb

Darn typo. That should be
StrConv(cell.Value, 3)

GaryB
09-21-2007, 10:36 AM
Darn typo. That should be
StrConv(cell.Value, 3)

When I created a macro and put this code into it I get a compile error
syntax error
This is what I did for the macro.
Sub UPPERCASE()
For Eachcell In Activesheet.UsedRange
cell.Value = StrConv(cell.Value, 2) ' where 3 = vbProperCase
Next cell
End Sub

Thanks

Gary

Bob Phillips
09-21-2007, 10:49 AM
Sub UPPERCASE()
For Each cell In Activesheet.UsedRange
cell.Value = StrConv(cell.Value, 3) ' where 3 = vbProperCase
Next cell
End Sub

GaryB
09-21-2007, 10:59 AM
Thanks Everyone,

The last entry by El Xid did it. Worked like a charm.

Gary

Bob Phillips
09-21-2007, 11:29 AM
That wasn't by me, I just spotted the missing space.

GaryB
09-21-2007, 12:50 PM
That wasn't by me, I just spotted the missing space.

Sorry,
Didn't mean to take anything away from Ron. I was refering to the fix in the code you spotted. Any Way,

Thanks again to everyone.

Gary