ExcelBanter - View Single Post - how to turn automatically in CAPITALS parts of text in excel ?

Ron Rosenfeld

On Sat, 29 Sep 2007 18:51:16 -0700, wrote:

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