Stripping Formating from Header/Footers

[CG]

Can anyone help me with stripping the formating code from a header or
footer? This seems like a daunting task with all the possible
options. Is there a way to evaluate the header or footer to just get
the text back? Any hints on accomplishing this would be helpful.

 

[Joel]

There is no way of doing this except the old fashion way. Remove the string
at the beginning of the text that begoin with an aphersand

Sub remove_format()
MyString = ActiveSheet.PageSetup.LeftHeader

While Left(MyString, 1) = "&"
MyString = Mid(MyString, 2)
If IsNumeric(Left(MyString, 1)) Then
'code is a number indicating font size
'remove number
Do While (IsNumeric(Left(MyString, 1)))
MyString = Mid(MyString, 2)
Loop
Else
If Left(MyString, 1) = "P+" Or _
Left(MyString, 1) = "P-" Then
'code is a Page Number
MyString = Mid(MyString, 3)
'remove number
Do While (IsNumeric(Left(MyString, 1)))
MyString = Mid(MyString, 2)
Loop
Else
'test if &&, then leave one &
'indicates a real & character
If Left(MyString, 1) <> "&" Then
'code is only a single letter
'remove single letter
MyString = Mid(MyString, 2)
End If
End If
End If
Loop
End Sub

 

[CG]

There is no way of doing this except the old fashion way. Remove the string
at the beginning of the text that begoin with an aphersand

Sub remove_format()
MyString = ActiveSheet.PageSetup.LeftHeader

While Left(MyString, 1) = "&"
MyString = Mid(MyString, 2)
If IsNumeric(Left(MyString, 1)) Then
'code is a number indicating font size
'remove number
Do While (IsNumeric(Left(MyString, 1)))
MyString = Mid(MyString, 2)
Loop
Else
If Left(MyString, 1) = "P+" Or _
Left(MyString, 1) = "P-" Then
'code is a Page Number
MyString = Mid(MyString, 3)
'remove number
Do While (IsNumeric(Left(MyString, 1)))
MyString = Mid(MyString, 2)
Loop
Else
'test if &&, then leave one &
'indicates a real & character
If Left(MyString, 1) <> "&" Then
'code is only a single letter
'remove single letter
MyString = Mid(MyString, 2)
End If
End If
End If
Loop
End Sub

Joel,

That is what I thought. Your code is a good start. My concern is
this will only work if all the formating code is at the front of the
string. What if it is something very complex like:

"&D&Z&F
&"Arial,Bold"&12&UCustom Header&"Arial,Regular"&10&U
&F&A"

I am sure it is possible however for me to write the code it would end
up being very long and I doubt I would ever work through the logic. I
was hoping someone had done it.