Formatting text

[Keyrookie]

Hey all,

I need to have a column of text represent key musical signatures, ie
Ab, Bb, C, F, etc. I'm trying to have the "b" be superscript. I nee
a condtional formula to change the lower case "b" to superscript.

Thanks for your help,

Keyrooki

 

[Ashish Mathur]

Hi,

You may use the UPPER() function in a spare column. This will convert all
the alphabets to upper case

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Billy Liddel]

Hi try this macro it also converts # to superscript so delete

Or sChr = "#"

if you do not want this.

Copy the code into the VB Editor (ALT + F11), Insert Modue, Paste the code
in module. Ctrl + Q to return to sheet.

Select the cells to format (I'd copy them first) and run the macro

ALT + F8, select macro and click Run.

Sub FormatFont()
Dim c
Dim sChr As String
Dim iLen As Integer
Dim i As Integer

'select the music
For Each c In Selection
'format each lower case b
For i = 1 To Len(c)
sChr = Mid(c, i, 1)
If sChr = "b" Or sChr = "#" Then
With ActiveCell.Characters(Start:=i, Length:=1).font
.Name = "Perpetua"
.FontStyle = "Italic"
.Superscript = True
End With
End If
Next i

Next
End Sub

Hope this helps.

Peter

Set the security level to Medium

 

[Billy Liddel]

whoops I only tested on one cell use this instead

Sub FormatFont()
Dim c
Dim sChr As String
Dim iLen As Integer
Dim i As Integer

'select the music
For Each c In Selection
c.Select
'format each lower case b and #
For i = 1 To Len(c)
sChr = Mid(c, i, 1)
If sChr = "b" Or sChr = "#" Then
With ActiveCell.Characters(Start:=i, Length:=1).font
.Name = "Perpetua"
.FontStyle = "Italic"
.Superscript = True
End With
End If
Next i

Next
End Sub

Just one extra line but..!

Peter