capitalize first letter

[rodchar]

hey all,
is there an easy way to capitalize just the first letter in a column and
leave the rest as is?

for example,
pubID --> PubID

thanks,
rodchar

 

[Bernd P]

Hello,

You might want to use
=UPPER(LEFT(A1,1))&MID(A1,2)

but maybe
=PROPER(A1)

will also work for you.

Regards,
Bernd

 

[Jim Thomlinson]

This formula should do it for you...

=UPPER(LEFT(A1, 1)) & MID(A1, 2, 256)
were the text is in A1

 

[galimi]

=UPPER(LEFT(A1,1)) & RIGHT(A1,LEN(A1)-1)

Assuming the data you want to capitalize is in column A

 

[rodchar]

thanks everyone for the help,
rod.

 

[Mike H]

Hi,

Do it in the same column with a macro. Right click your sheet tab, view code
and paste this in and run it.

Sub versive()
Lastrow = Worksheets("data").Cells(Rows.Count, "A").End(xlUp).Row
For Each r In Worksheets("data").Range("A1:A" & Lastrow)
If Not r.HasFormula Then
r.Value = UCase(Left(r.Value, 1)) & Mid(r.Value, 2)
End If
Next
End Sub

Mike

 

[Dave Peterson]

One more...

=UPPER(LEFT(A1, 1)) & lower(MID(A1, 2, len(a)))

 

[Dave Peterson]

Ignore my suggestion. I didn't read the question.

 

[rodchar]

thanks all for the help,
rod.

 

[nflor009]

A follow up question on this topic:

How do I format a cell so that whatever is entered has the first letter
capitalized, as in a proper name. I tried the =PROPER(cell) but a formula
doesn't work since you delete it when you type into that cell, so I need
formatting, not a formula.

 

[nflor009]

That's a bit over my head. I opened VBA from the spreadsheet, selected the
sheet I was working on, but have no clue what to put in the window that
opened up after that.

I'd greatly appreciate if someone could tell me what to insert into that
window that might work for a range of cells in a column where I want the
first letter of whatever goes in that cell to be capitalized.

Maybe it's silly but the person inputting the data hates using her shift
key, so everything is in small letters and when I need to export certain
cells into the salutation of group merge letter in Word, I don't want to have
to correct the output.

 

[Don Guillett]

Right click sheet tab>view code>copy/paste this>change range to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("c2:c12")) Is Nothing _
Or Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Target.Value = WorksheetFunction.Proper(Target)
Application.EnableEvents = True
End Sub

 

[nflor009]

Thanks for responding to this, Don. I did exactly what you said, and the
cells acted no differently. I typeed a word into them and the first letter is
still not capitalized. I even tried it again leaving c2:c12 in there and
those cells didn't respond either. Am I supposed to do something to apply the
changes after pasting the code? I also tried it with something in that range
of cells before adding the code to see if it would work....but it didn't.

 

[Don Guillett]

sheet tab
Did you put in the sheet module or a regular module.