autocorrect options

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I import names and address in ascii format into excel. All the letters are
lower case and I need to change the First Letter of the names to Upper case.
I thought having
AutoCorrect set to Capitalize first letters of sentences would automatically
change them to upper case but that doesn't seem to be working. What do I need
to do to get the First letter of the name changed to upper case.. Or does
someone have a macro written they'd share with me to do this..

thanks in advance
d
 
d

As you have discovered, AutoCorrect does not work with this data.

Excel has the PROPER Function that will do what you want in a "helper" column.

Assume Names are in Column A.

A1 contains gord dibben

In B1 enter =PROPER(A1)

B1 returns Gord Dibben

Drag/copy B1 down as far as you wish.

If/when happy with results, copy column B and (in place) Paste
Special>Values>OK>Esc.

Delete original Column A.

Alternative.........

A macro to change all selected cells to Proper Case.

Sub Proper_Case()
Dim Cell As Range
Dim selected As Range
On Error Resume Next
Set selected = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selected Is Nothing Then Exit Sub
For Each Cell In selected
Cell.Formula = Application.Proper(Cell.Formula)
Next
End Sub

Gord Dibben Excel MVP
 
PERFECT Thanks so much..

d

Gord Dibben said:
d

As you have discovered, AutoCorrect does not work with this data.

Excel has the PROPER Function that will do what you want in a "helper" column.

Assume Names are in Column A.

A1 contains gord dibben

In B1 enter =PROPER(A1)

B1 returns Gord Dibben

Drag/copy B1 down as far as you wish.

If/when happy with results, copy column B and (in place) Paste
Special>Values>OK>Esc.

Delete original Column A.

Alternative.........

A macro to change all selected cells to Proper Case.

Sub Proper_Case()
Dim Cell As Range
Dim selected As Range
On Error Resume Next
Set selected = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selected Is Nothing Then Exit Sub
For Each Cell In selected
Cell.Formula = Application.Proper(Cell.Formula)
Next
End Sub

Gord Dibben Excel MVP
Click to expand...
 
Opps, one more question
The data has the 9 digit zip code and I'd like to either parse it only use
five digits and or put in hyphen between the 5 and 6th digits. Can you help
me with this one..
The address is in the format of City State Zip all within one cell. In some
cases the last four digits are 0000 and therefore not correct and I'd like to
remove these if you assist me with entering the hyphen...

thanks again for the prior answer it was WONDERFUL..:)

david
 
Back
Top