Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to turn automatically in CAPITALS parts of text in excel ?
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to turn automatically in CAPITALS parts of text in excel ?
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 In article . com, 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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to turn automatically in CAPITALS parts of text in excel ?
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to turn automatically in CAPITALS parts of text in excel ?
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 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing parts of a text file into Excel | Excel Discussion (Misc queries) | |||
Turn off cursor from automatically highlighting cells in Excel? | Excel Worksheet Functions | |||
Text formatted as All Capitals? | New Users to Excel | |||
Text All Capitals | New Users to Excel | |||
Combine & Display “Fixed” & “Automatically Updated” Date Parts | Excel Worksheet Functions |