Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find partial cell format
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. -- Thank you, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find partial cell format
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 wrote: 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. -- Thank you, -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find partial cell format
Do you have sample code I could see? Played with VBA - working on expanding
my knowledge but mostly in Access. -- Thank you, gig "Dave Peterson" wrote: 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 wrote: 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. -- Thank you, -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find partial cell format
Have you tried just selecting all cells and FormatCellsFont and unchecking
Strikethrough? Gord Dibben MS Excel MVP On Fri, 20 Jul 2007 10:04:04 -0700, icq_giggles wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find partial cell format
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. -- Thank you, gig "Gord Dibben" wrote: Have you tried just selecting all cells and FormatCellsFont and unchecking Strikethrough? Gord Dibben MS Excel MVP On Fri, 20 Jul 2007 10:04:04 -0700, icq_giggles wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find partial cell format
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 icq_giggles wrote: Do you have sample code I could see? Played with VBA - working on expanding my knowledge but mostly in Access. -- Thank you, gig "Dave Peterson" wrote: 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 wrote: 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. -- Thank you, -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find partial cell format
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! -- Thank you, gig "Dave Peterson" wrote: 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 icq_giggles wrote: Do you have sample code I could see? Played with VBA - working on expanding my knowledge but mostly in Access. -- Thank you, gig "Dave Peterson" wrote: 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 wrote: 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. -- Thank you, -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find partial cell format
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. icq_giggles wrote: 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! -- Thank you, gig "Dave Peterson" wrote: 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 icq_giggles wrote: Do you have sample code I could see? Played with VBA - working on expanding my knowledge but mostly in Access. -- Thank you, gig "Dave Peterson" wrote: 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 wrote: 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. -- Thank you, -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using SUMIF and FIND/RIGHT to add values of a partial cell referen | Excel Discussion (Misc queries) | |||
find partial text contained in another cell | Excel Discussion (Misc queries) | |||
Shade partial cell | Excel Discussion (Misc queries) | |||
find partial values in cell | Excel Worksheet Functions | |||
Finding Partial Text in a Cell | Excel Worksheet Functions |