Individual Character fonts in a cell

[gtslabs]

I have about 30,000 rows of data to filter, all in Col A
The problem is that some of the individual characters in each cell are
hidden (font white).

I want to remove those hidden characters from the cell.
Is it possible to loop thur each character in a cell, search for a
white font and removed that character?

 

[Rick Rothstein]

With 30,000 rows of text to process one character at a time, I think you
will need to be patient when waiting for the results; however, I believe the
following macro will do what you want...

Sub DeleteWhiteCharacters()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim Chars As String
On Error GoTo Whoops
Application.ScreenUpdating = False
With Worksheets("Jan 09")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 1 To LastRow
Chars = .Cells(X, "A").Text
For Z = 1 To Len(Chars)
If .Cells(X, "A").Characters(Z, 1).Font.ColorIndex = 2 Then
Mid(Chars, Z, 1) = Chr(1)
End If
Next
.Cells(X, "A").Clear
.Cells(X, "A").Value = Replace(Chars, Chr(1), "")
Next
End With
Whoops:
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

I forgot to mention (although I think you would have noticed)... change my
example worksheet name in the With statement to whatever your actual
worksheet is named.

 

[Petr Danes]

Excel also has the FindFormat method in VBA and the format option in the GUI
Find/Replace dialog. Either will probably be a good deal faster then
manually looping through each cell in 30,000 rows.

Pete

 

[Ron Rosenfeld]

Excel also has the FindFormat method in VBA and the format option in the GUI
Find/Replace dialog. Either will probably be a good deal faster then
manually looping through each cell in 30,000 rows.

Pete

And what happened when you tested your suggestions against a cell in which just
a few letters were differentially formatted, as the OP requested?
--ron

 

[Dave Peterson]

It didn't work for me in xl2003.

But I figured I did something wrong.

It always came back that it couldn't find any matches to change.

I used asdfqwerasdf in a bunch of cells with qwer in white and both a&f's
automatic and s&d's in red.

 

[Ron Rosenfeld]

It didn't work for me in xl2003.

But I figured I did something wrong.

It always came back that it couldn't find any matches to change.

I used asdfqwerasdf in a bunch of cells with qwer in white and both a&f's
automatic and s&d's in red.

It didn't work for me, either, in 2007. My thought had been that that option,
in both the worksheet and also VBA, is looking at the CELL format, and not at
the format of individual characters within the cell.

That's why I asked the recommender what happened with *his* testing.
--ron

 

[Dave Peterson]

After I read your initial post, I tried the edit|replace stuff. And it didn't
work. (So no post from me in this thread.)

Then I read Petr's post and tested again. I gave it a shot once more fiddling
with all the "clear" buttons on the dialogs. It didn't work for me then,
either.

 

[Rick Rothstein]

Your posting raised made me realize my code needed to be modified. Why? I
didn't take into account that other characters may be colored with colors
other than white or the default (usually black)... my prior code makes every
character the default color (usually black). Here is modified code which
preserves the existing non-white character colors while deleting the white
characters...

Sub DeleteWhiteCharacters()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim Chars As String
Dim Colors() As String
On Error GoTo Whoops
Application.ScreenUpdating = False
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 1 To LastRow
Chars = .Cells(X, "A").Text
ReDim Colors(1 To Len(Chars))
For Z = 1 To Len(Chars)
If .Cells(X, "A").Characters(Z, 1).Font.ColorIndex = 2 Then
Mid(Chars, Z, 1) = Chr(1)
Colors(Z) = "XX"
Else
If .Cells(X, "A").Characters(Z, 1).Font.ColorIndex < 0 Then
Colors(Z) = "00"
Else
Colors(Z) = Format(.Cells(X, "A").Characters(Z,
1).Font.ColorIndex, "00")
End If
End If
Next
.Cells(X, "A").ClearContents
.Cells(X, "A").Value = Replace(Chars, Chr(1), "")
Colors =
Split(Application.WorksheetFunction.Trim(Replace(Join(Colors), "XX", "")))
For Z = 1 To UBound(Colors)
If Colors(Z - 1) <> "00" Then
.Cells(X, "A").Characters(Z, 1).Font.ColorIndex = CLng(Colors(Z -
1))
End If
Next
Next
End With
Whoops:
Application.ScreenUpdating = True
End Sub

 

[Dave Peterson]

The real bad news is that if you don't keep track of the formatting (font, font
color, super/sub script, strikethrough, ...) on a character by character basis,
you'll end up with the formatting attribute for the first character (not the
default, IIRC).

 

[Rick Rothstein]

No, you are correct. The code could be modified easily enough by creating a
one-dimensional array for each font attribute that a character could
possibly have and then doing to them exactly what I did to the Colors
array... messy, but doable. Excel's method of addressing individual
character attributes is not very friendly... VB should have implemented a
Font object that would inherit all the attributes with the setting of an
object variable to that Font object... I think that would have made things
easier.

 

[Dave Peterson]

I've done that a few times:
http://snipurl.com/9fe4i [groups_google_com]

But doing it character by character over a wide range of cells is really slow.

And it may have made your life easier, but not if you were implementing it for
MS <vbg>.

No, you are correct. The code could be modified easily enough by creating a
one-dimensional array for each font attribute that a character could
possibly have and then doing to them exactly what I did to the Colors
array... messy, but doable. Excel's method of addressing individual
character attributes is not very friendly... VB should have implemented a
Font object that would inherit all the attributes with the setting of an
object variable to that Font object... I think that would have made things
easier.

 

[Ron Rosenfeld]

Your posting raised made me realize my code needed to be modified. Why? I
didn't take into account that other characters may be colored with colors
other than white or the default (usually black)... my prior code makes every
character the default color (usually black). Here is modified code which
preserves the existing non-white character colors while deleting the white
characters...

I was going to post this before, then changed my mind. But since you are off
on this tangent

It may not apply to the OP, since he did specify "white". But he also
specified "invisible" which to me would mean deleting characters that are
formatted in the same color as the background (interior) color.
--ron