Replacing capital letters in cells

[budgy]

Help, quick question,

I need to format a excel document with lots and lots of data entries, i need to change the first letter from a capital to a normal letter is there a quick way of doing this for the whole colum?

Thanks


"He that asks a question is a fool for a moment, he that never asks a question is a fool all his life

 

[MarcLodge]

Budgy,

is the PROPER function of any help?

Marc

 

[budgy]

yes Marc,

thanks, but how do i apply that function to a certain number of rows?..

Ive worked out how to apply it to a single cell, but i need to apply it to cells A2 to A121...

"He that asks a question is a fool for a moment, he that never asks a question is a fool all his life

 

[MarcLodge]

Budgy,

Do you need to apply the function to the data in situ i.e. you want the data in A1 to stay in A1 and not be copied to anywhere else? Also, does each cell have more than one word in it, and if so do all words need capitalizing, or just the first one?

Marc

 

[MarcLodge]

Budgy,

create a macro out of the following code:

Sub TextProperCase()
Dim rng As Range, cell As Range
Set rng = Intersect(ActiveSheet.UsedRange, Selection)
For Each cell In rng
If cell.HasFormula = False Then
cell.Value = StrConv(cell.Value, vbProperCase)
End If
Next cell
End Sub

Highlight the cells you want capitalized and run the macro.

Hope this helps.

Marc

 

[xlbo]

alternatively, put

=lower(A2)

in B2 and copy down
copy B2:B121 and paste special>values back over A2:A121
then delete formulae in B2:B121

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MarcLodge]

Geoff,

er.... don't you mean put

=proper(a2)



Marc

 

[KenWright]

Not the way I read the OP:-

i need to change the first letter from a capital to a normal letter...

Was going to chime in earlier and point that out, but decided against it, but seeing as you have reiterated it

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Doesn't mean that's what he actually wanted of course, as his reply to you would indicate the contrary, but that is actually what he asked for.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

lol

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question