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.

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" wrote:

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.

On Feb 15, 11:27 am, Joel wrote:
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" wrote:
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,

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.