Individual Character fonts in a cell
 

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?

Individual Character fonts in a cell
 

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 (MVP - Excel)


"gtslabs" wrote in message
...
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?

Individual Character fonts in a cell
 

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"gtslabs" wrote in message
...
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?

Individual Character fonts in a cell
 

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



"Rick Rothstein" píse v diskusním
príspevku ...
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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"gtslabs" wrote in message
...
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?

Individual Character fonts in a cell
 

On Sat, 3 Jan 2009 15:09:38 +0100, "Petr Danes" wrote:

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

Individual Character fonts in a cell
 

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

On Sat, 3 Jan 2009 15:09:38 +0100, "Petr Danes" wrote:

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

Individual Character fonts in a cell
 

On Sat, 03 Jan 2009 12:41:38 -0600, Dave Peterson
wrote:

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

Individual Character fonts in a cell
 

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.



Ron Rosenfeld wrote:

On Sat, 03 Jan 2009 12:41:38 -0600, Dave Peterson
wrote:

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

Individual Character fonts in a cell
 

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(J oin(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

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
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 wrote:

On Sat, 3 Jan 2009 15:09:38 +0100, "Petr Danes"
wrote:

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

Individual Character fonts in a cell
 

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

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(J oin(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

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
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 wrote:

On Sat, 3 Jan 2009 15:09:38 +0100, "Petr Danes"
wrote:

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

--

Dave Peterson

Individual Character fonts in a cell
 

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.

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
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 wrote:

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(J oin(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

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
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 wrote:

On Sat, 3 Jan 2009 15:09:38 +0100, "Petr Danes"

wrote:

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

--

Dave Peterson

Individual Character fonts in a cell
 

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.

Rick Rothstein wrote:

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.

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
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 wrote:

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(J oin(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

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
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 wrote:

On Sat, 3 Jan 2009 15:09:38 +0100, "Petr Danes"

wrote:

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

--

Dave Peterson

--

Dave Peterson

Individual Character fonts in a cell
 

On Sat, 3 Jan 2009 19:16:41 -0500, "Rick Rothstein"
wrote:

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