how to turn automatically in CAPITALS parts of text in excel ?

[sotnasonun]

I have a huge catalogue of music (thousands of records) made in excel
where I have in the first cell the name of the band + album + format,
something like this:

Pearl Jam - name of the album - CD

how can I turn automatically all names of bands into CAPITALS, in
example:

PEAR JAM - name of the album - CD


Is there a way to run a script where it will turn in CAPITALS all text
from the beginning of a cell 'till the symbol ( - ) of each cell ?
(For a particluar column)

 

[JE McGimpsey]

One way:

Public Sub CapitalizeToHyphen()
Dim rCell As Range
Dim rToCheck As Range
Dim nPos As Long

On Error Resume Next
Set rToCheck = Selection.SpecialCells( _
xlCellTypeConstants, xlTextValues)
On Error GoTo 0
If rToCheck Is Nothing Then Exit Sub
For Each rCell In rToCheck
With rCell
nPos = InStr(.Text, "-")
If nPos > 0 Then _
.Value = UCase$(Left$(.Text, nPos - 1)) & Mid(.Text, nPos)
End With
Next rCell
End Sub

 

[Ron Rosenfeld]

I have a huge catalogue of music (thousands of records) made in excel
where I have in the first cell the name of the band + album + format,
something like this:

Pearl Jam - name of the album - CD

how can I turn automatically all names of bands into CAPITALS, in
example:

PEAR JAM - name of the album - CD


Is there a way to run a script where it will turn in CAPITALS all text
from the beginning of a cell 'till the symbol ( - ) of each cell ?
(For a particluar column)

One way might be to use an event macro:

=============================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a As Range
Dim t As String
Set a = [A:A] 'range where Albums stored

If Not Intersect(Target, a) Is Nothing Then
t = Left(Target.Text, InStr(1, Target.Text, "-"))
Target.Value = Replace(Target.Text, t, UCase(t))
End If

End Sub
===============================

To enter this, right click on the sheet tab and select View Code. Paste the
above macro into the window that opens.

set a = whatever the range is where you'll be entering the albums.
--ron

 

[Gord Dibben]

This macro will do the job.

Just select the top cell in the column you want to change.

Sub Upper_Bandname()
Dim rng As Range, cell As Range
Dim iloc As Long
Set rng = Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp))
For Each cell In rng
iloc = InStr(cell.Formula, "-")
If iloc = 0 Then
If Len(Trim(cell.Formula)) > 1 Then
cell.Font.Bold = True
cell.Formula = UCase(cell.Formula)
End If
Else
cell.Formula = UCase(Left(cell.Formula, iloc - 1)) & _
Right(cell.Formula, Len(cell.Formula) - iloc + 1)

'if you want bold type remove the ' from the next two lines.
' cell.Characters(Start:=1, Length:=iloc - 1). _
' Font.FontStyle = "Bold"

End If
Next
End Sub


Gord Dibben MS Excel MVP