Find partial cell format

[icq_giggles]

I have cells that have part of the cell formatted with strikethrough font.
All of these also have text that is not strikedthrough. Is there a way to
find and delete the strikethrough text? The "Find" with the strikethrough
format selected doesn't work - I assume because of the mixed format. If I
tell it use selected cell formating - it selects the regular format and
therefore finds ALL the cells in the column.

 

[Dave Peterson]

I think you'll have to loop through the cells in the range you want to fix.

Then loop through each character to find the character(s) to delete.

 

[icq_giggles]

Do you have sample code I could see? Played with VBA - working on expanding
my knowledge but mostly in Access.

 

[Gord Dibben]

Have you tried just selecting all cells and Format>Cells>Font and unchecking
Strikethrough?


Gord Dibben MS Excel MVP

 

[icq_giggles]

Actually I wanted to find the strikethrough text so I did do a find looking
for the strikethrough font, however I have mixed formatting in the field (not
my data just trying to clean it up for an Access import) i.e. multiple
serial numbers but showing which ones were changed by using the
strikethrough, but leaving the good ones in the same cell. I'm trying to
pull out the "good" ones. Could have up to 7 in a single cell with 1 to 6 of
them being "good" or 1 to 6 being strikethrough.

and yes - I tried the choose format from cell option also.

 

[Dave Peterson]

What is this thing called Access? I thought that if the job had to be done, it
should be done in Excel <vbg>.

Test this against a copy--or close without saving--just in case!

Option Explicit
Sub testme01()
Dim wks As Worksheet
Dim rng As Range
Dim myCell As Range
Dim myStrikeThrough As Variant 'true/false/null (for mixture)
Dim iCtr As Long
Dim myStr As String

Set wks = Worksheets("sheet1") '<- change as required

With wks
Set rng = Nothing
On Error Resume Next
Set rng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
End With

If rng Is Nothing Then
'no constants to check
Exit Sub
End If

For Each myCell In rng.Cells
myStrikeThrough = myCell.Font.Strikethrough
If myStrikeThrough = False Then
'do nothing
ElseIf myStrikeThrough = True Then
'get rid of all the characters
myCell.ClearContents
Else
myStr = ""
For iCtr = 1 To Len(myCell.Value)
If myCell.Characters(Start:=iCtr, Length:=1) _
.Font.Strikethrough = True Then
'do nothing
Else
myStr = myStr & Mid(myCell.Value, iCtr, 1)
End If
Next iCtr
myCell.Value = myStr
End If
Next myCell
End Sub

Do you have sample code I could see? Played with VBA - working on expanding
my knowledge but mostly in Access.

 

[icq_giggles]

Thank you Dave - your post just showed on my pc.

LOL - I love both programs - they give me job security LOL.

I tried it and it removed some of the strikethrough text and placed the
strikethrough formatting on my regular text in the cell (that it kept). This
was only on the cells where the strikethrough text appeared first. Other
cells it worked great, and for my purposes (Access will strip the formatting)
this will probably work, but for other users I wanted to flag this issue.

Thank you so much for your help!

 

[Dave Peterson]

I never tested it where the first character had a strikethrough font.

Add one line right after this:
myCell.Value = myStr
myCell.Font.Strikethrough = False '<- added

Thanks for the correction.

Thank you Dave - your post just showed on my pc.

LOL - I love both programs - they give me job security LOL.

I tried it and it removed some of the strikethrough text and placed the
strikethrough formatting on my regular text in the cell (that it kept). This
was only on the cells where the strikethrough text appeared first. Other
cells it worked great, and for my purposes (Access will strip the formatting)
this will probably work, but for other users I wanted to flag this issue.

Thank you so much for your help!