Finding an underlined character among several characters in a cell

[Guest]

I have several characters in a cell, for example "7432". I chose text format
so that I could underline one of the characters, for example the "3". How do
I find out programmatically that the 3 is the character that is underlined?

Assume that the value is in Cells(2,1). I know a loop is involved and I
don't have too much trouble programming but I don't see something that will
tell me the format of a specific character.

Here's some code I started:
Function FindUnderline(ByVal MyNumber)
Dim l_length As Integer
l_length = Len(Cells(2, 1))
Dim l_Position As Integer
Dim l_String As String

For l_Position = 1 To l_length
l_String = Mid(Cells(2, 1), l_Position, 1)
Next l_Position
End Function


Thanks for you help

 

[Dave Peterson]

One way:

Option Explicit

Sub testme()
Dim myVal As Long
myVal = FindUnderline(Range("a1"))
If myVal = 0 Then
MsgBox "no characters underlined"
Else
MsgBox "first underline at: " & myVal
End If
End Sub

Function FindUnderline(ByVal myCell As Range) As Long

Dim l_Position As Integer

FindUnderline = 0
For l_Position = 1 To Len(myCell.Value)
If myCell.Characters(Start:=l_Position, Length:=1).Font.Underline _
= xlUnderlineStyleNone Then
'keep looking
Else
FindUnderline = l_Position
Exit Function
End If
Next l_Position
End Function

If you look at VBA's help, you'll see:
XlUnderlineStyle can be one of these XlUnderlineStyle constants.
xlUnderlineStyleNone
xlUnderlineStyleSingle
xlUnderlineStyleDouble
xlUnderlineStyleSingleAccounting
xlUnderlineStyleDoubleAccounting

(if you wanted to look for a particular style)

 

[Jon Peltier]

Try this one:

Sub ExtractUnderlined()
Dim sUnder As String
Dim i As Integer
Dim n As Integer

n = Len(ActiveCell.Text)

If n > 0 Then
For i = 1 To n
If ActiveCell.Characters(i, 1).Font.Underline _
<> xlUnderlineStyleNone Then
sUnder = sUnder & ActiveCell.Characters(i, 1).Text
End If
Next
End If

If Len(sUnder) > 0 Then
MsgBox "Characters " & sUnder & " in the active cell are underlined", _
vbInformation, "Underlined Characters"
End If

End Sub

You can't just examine the string, because that's just the text. You need to examine
the characters in the cell.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______