Solved: finding Strikethrough font

[vzachin]

hi,

I have a column A that contains data separated by a space. some of the data has a strikethrough font

is it possible to place in the adjacent column B any of the data from column A that does not contain a strikethrough font?

please see attached

thanks
zach

[dominicb]

Good evening vzachin

It's tempting fate to ever say never with Excel (so I won't go that far) but I don't think that you can check the formatting of individual characters within a cell. You can check whether the whole cell has strikethrough applied or whether the whole cell hasn't but try intoducing half measures and it falls over.

HTH

DominicB

[tstav]

Check this
[vba]Sub RemoveStrikethroughCharacters()
Dim i As Long
Dim rng As Range, cel As Range

Application.ScreenUpdating = False

'This is the range of the cells in col "A"
Set rng = Range(Range("A5"), Range("A" & Rows.count).End(xlUp))

'Clear the column that will receive the results
rng.Offset(0, 1).ClearContents

For Each cel In rng
'Check each character in the cell
For i = 1 To Len(cel.Value)
'If it is not Strikethrough, write it in col "B"
If cel.Characters(i, 1).Font.Strikethrough = False Then
cel.Offset(0, 1).Value = cel.Offset(0, 1).Value & cel.Characters(i, 1).Text
End If
Next
'Remove leading/trailing spaces
cel.Offset(0, 1).Value = Trim(cel.Offset(0, 1).Value)
'Remove double spaces from within cell content
cel.Offset(0, 1).Value = Replace(cel.Offset(0, 1).Value, " ", " ")'<-- there are 2 spaces in first " " (editor removed one)
Next

Application.ScreenUpdating = True
End Sub
[/vba]

Edit: Turn screenupdating on and watch the characters being typed one after the other. Makes a cute visual effect... (if you ignore the flickering).

[vzachin]

tstav,

that is simply amazing coding!



thanks so much!
zach

ps dominicb
thanks for your thoughts on this

[xluser2007]

Hi All,

I stumbled onto this thread as I had a similar query (which has been answered now thanks to tstav ).

tstav, brilliant code. Just one query, how does the following line work?

[vba] 'Remove double spaces from within cell content
cel.Offset(0, 1).Value = Replace(cel.Offset(0, 1).Value, " ", " ") [/vba]
It seems to be replacing " " with " " i.e.e the same thing. This is just for me to learn and understand VBA better.

Thanks

[tstav]

Hi xluser2007,

Check the comment I have added in the Replace code line in post#3.

As I'm saying there, the second argument is two spaces and not one, but the site's editor (the one we use to write our posts) removes any extra spaces, in order to save space. So... it trimmed my two-space string too... (I forgive the editor , for it has been nice to me in all other counts... )

Obviously, I put the comment only after you had read my post. My apologies...

[xluser2007]

Hi tstav,

Always great learning from you!

Thanks, I now realise that it was just the editor playing mind games .

Cheers