Individual character properties in Excel

Topic

New Reply

I wish to identify individual character properties in a cell in Excel, eg to tell whether character 53 is bold or unbold. I normally operate in the old Macro 4 programming environment, but understand this request may need Visual Basic. Could anyone help please?

Reply | Quote

Replies

For example:

Code:

Msgbox activecell.Characters(53,1).Font.Bold

Reply | Quote

Rory,

Thank you. Unfortunately my knowledge of VB is very limited and I’d appreciate more assistance if possible. I’m hoping to convert all non-bold characters to spaces in situ (ie convert a selection of cells so that all non bold characters are spaces). If you have the time to show me a bit more I’d be very grateful.

Geoffrey

Reply | Quote

Sure, it would basically be:

Code:

Sub ReplBold()
    Dim rngCell As Range
    Dim n As Long
    
    Application.ScreenUpdating = False
    For Each rngCell In Selection
        With rngCell
            ' can't format parts of formulas
            If Not .HasFormula Then
                For n = 1 To Len(.Value)
                    If Not .Characters(n, 1).Font.Bold Then .Characters(n, 1).Text = " "
                Next n
            End If
        End With
        
    Next rngCell
    
    Application.ScreenUpdating = True
End Sub

Reply | Quote

You might want to alter just one line of Rory’s solution

Change

Code:

If Not .HasFormula  Then

to

Code:

If Not .HasFormula And Application.IsText(rngCell) Then

Otherwise you will get an error if any of the selected cells have a Date or a Number in

Reply | Quote

My sincere thanks to both of you. I haven’t yet had time to check the suggested change but will do. The code works like a charm with ONE exception. It does not work for cells with 256 characters or more. I’m using Excel 2003. I know that there are some limitations at this number and it may be that it cannot be solved in this version of Excel. Even if that is the case, it’s a huge step forward for me, as I can go through and manually alter if necessary.

(My application seeks to identify names in text and to check for validity against a database. Most names are in bold. I do some manual adjustments (eg non bold to bold and vice versa) and the rest is code. The bit you’ve helped with so kindly is the one part I could not do myself.)

Reply | Quote

Not sure why Rory’s method does not handle 256+ cells.

I did find out that for some reason the IsText Function Returns False IF there are greater than 256 characters
and it does that in 2003, 2007 and 2010 (might be a bug there or I made a mistake, not sure which)

Anyway the code below (a bit longer), but does the trick

Code:

Sub NonBoldCharsToSpace()

Dim rngCell As Range
Dim intChar As Integer
Dim strNEW As String

'Check every Cell
For Each rngCell In Selection
    'First Need to Text for a Formula and also just a plain number
    'If Not rngCell.HasFormula And Not IsNumeric(rngCell) Then
    If (Not rngCell.HasFormula) And (Application.IsText(rngCell) Or Len(rngCell) > 256) Then
        'If Just Text Data then loop through each 0 to character count -1 and Build New String
        strNEW = ""
        For intChar = 1 To rngCell.Characters.Count
            If rngCell.Characters(intChar, 1).Font.Bold = False Then
                strNEW = strNEW & " "
            Else
                strNEW = strNEW & rngCell.Characters(intChar, 1).Text
            End If
        Next
        'Now Bold the Cell
        rngCell = strNEW
        rngCell.Font.Bold = True
    End If
Next

End Sub

Reply | Quote

Again, thanks to both of you. It’s increased productivity in this area by a factor of sixty or more, and if I take the whole application, it’s whole increases in accuracy and productvity. That’s a great step forward!

I did change the “>256” to “>255” as I found it got stuck on 256 exactly. These underlying bugs are certainly tenacious!

Reply | Quote