Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    For example:
    Code:
    Msgbox activecell.Characters(53,1).Font.Bold
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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
    Andrew

  6. #6
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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.)

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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
    Andrew

  8. #8
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •