Text formatting

  • Thread starter Thread starter Michel Khennafi
  • Start date Start date
M

Michel Khennafi

Hi gurus...

I would like to run it by you, experts... Here is the problem: I woul like
to build a macro that format the text so that the first letter of each word
is a capital...

For Instance a1= "perform backup"
I would like the macro to transform the entry in a1= "Perform Backup"... Can
someone provide guidance as to what the macro should look like or what
function to use?

Many thanks from Brew City

Michel
 
Try in B1: =PROPER(A1)
Copy B1 down

If desired, copy col B and overwrite values in col A via:
Copy col B > Paste special on col A > Check "Values" > OK

--

Alternatively, try the sub below:

Sub ProperCase()
'Select range of cells and run the sub
Dim Cell As Range
For Each Cell In Selection
Cell = Application.Proper(Cell)
Next
End Sub
 
Max

I hope you realise your code will turn any formulas to values. I would add
this caveat if OP knows there would be formulas in the selected range.

The following code will leave formulas intact.

Sub Proper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = Application.Proper(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub

For a probably faster and more fool-proof macro visit David McRitchie's site
to see his code.

http://www.mvps.org/dmcritchie/excel/proper.htm

Gord Dibben Excel MVP
 
Gord,

Many thanks for the clarifications
and much better alternatives !

Perhaps I might have been too presumptious
in reading the post ..
 
It's probably better to use the StrConv Function over the Proper
Worksheet Function. It's also a good idea to use the SpecialCells
method to also help things along. Also, switching Calculatiuon into
Manual while looping can help greatly.

Sub ProperText()
Dim rCell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
For Each rCell In Selection.SpecialCells _
(xlCellTypeFormulas, xlTextValues)
rCell.Formula = StrConv(rCell.Formula,
vbProperCase)
Next
On Error GoTo 0
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
Sub ProperText()
Dim rCell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
For Each rCell In Selection.SpecialCells _
(xlCellTypeFormulas, xlTextValues)
rCell.Formula = StrConv(rCell.Formula, _
vbProperCase)
Next
On Error GoTo 0
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
Back
Top