View Full Version : Solved: finding Strikethrough font
vzachin
04-18-2008, 08:19 AM
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
04-18-2008, 11:07 AM
Good evening vzachin
It's tempting fate to ever say never with Excel (so I won't go that far:think:) 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
04-18-2008, 11:36 AM
Check this
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
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
04-18-2008, 06:28 PM
tstav,
that is simply amazing coding! :thumb
:bow:
thanks so much!
zach
ps dominicb
thanks for your thoughts on this
xluser2007
04-18-2008, 07:36 PM
Hi All,
I stumbled onto this thread as I had a similar query (which has been answered now thanks to tstav :thumb).
tstav, brilliant code. Just one query, how does the following line work?
'Remove double spaces from within cell content
cel.Offset(0, 1).Value = Replace(cel.Offset(0, 1).Value, " ", " ")
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
04-19-2008, 09:15 AM
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
04-19-2008, 06:36 PM
Hi tstav,
Always great learning from you!
Thanks, I now realise that it was just the editor playing mind games :).
Cheers
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.