Excel Search and remove text by formatting.

[TheSmileyCoder]

I have an excel sheet, in which text has been formatted, and the format has a "meaning". For example text formatted as "Strike-through" means the text is deleted.

I now need to import this into Access 2003, and I need the values in Access to not include the text marked as deleted.

Therefore I would like to do a search and replace in Excel to remove all text marked with strikethrough. Any idea how to accomplish this?

Each cell can have multiple different formats, just to compliace things. Unlike Access where each cell can only have 1 format.
With love, TheSmileyOne

[Jim Doherty]

@TheSmileyOne
Quick and dirty ...set your own sheet name and ranges if it helps you :)

Function BlitzStrikeThrough(strfile As String)
    Dim xlApp As Object, xlWB As Object, IStartedXL As Boolean
    On Error Resume Next
    Set xlApp = GetObject(, "excel.application")
    On Error GoTo here
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("excel.application")
        IStartedXL = True
        End If
    Set xlWB = xlApp.Workbooks.Open(strfile)
    With xlApp.Worksheets("Sheet1")
        .Range("A1:H100").Select
        With Selection.Font
        .Strikethrough = False
        End With
        .Range("A1").Select
    End With
here:
    xlWB.Close True
    If IStartedXL Then xlApp.Quit
    Set xlWB = Nothing
    Set xlApp = Nothing
End Function

[TheSmileyCoder]

Thanks for your effort Jim, but thats not quite what I need. I don't need to remove the formatting, I need to remove the text parts that has a specific format. I.e. If its formatted as strikethrough, I need to delete that part of the text, without deleting the entire cell.

Basicly for each charecter in the cell, I need to check if its StrikeThrough, and if so, delete it. (Not the cell, the specific charecter.

[TheSmileyCoder]

I found out there is a property called Charecter which can be used on Range.

Public Sub DeleteStriked(myRange as Range)
  Dim intI as Integer
  Dim strNew as string
  for intI=1 to len(myRange)
    if myRange.Charecters(intI,1).font.Strikethrough then
      'We dont want this text
    Else
      'We do want this text
     strNew=strNew & myRange.Charecters(intI,1)
    End if
  next
End Sub

I can now use this function to remove the striked out text. Thank you for your help, both Jim who posted, and all those who took the time to look at this.

[Jim Doherty]

@TheSmileyOne
Elegant solution :) I obviously misunderstood your original..... apologies