Identify strikethrough text in excel cell

Question

0.00/5 (No votes)

See more:

Hi Everyone,

good day!

My problem is only portion of the excel text is Strikethrough i want to delete the said Strikethrough text

example:

The quick brown fox jump over the lazy dog

let say: brown word is Strikethrough. how can i delete the said word, and return only those words were not in Strikethrough.

Thanks
Chaegie

Posted 5-Jan-10 1:57am

Chaegie

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

aaaehb · Answer 1 · 2010-01-05T06:54:00

Let's suppose that your text "The quick brown fox jump over the lazy dog" is in the first cell of the excel sheet, means cell (1,1), with the word "brown" is StrikeThrough

Then make a button or a macro and edit it to be as follows:

Sub Button1_Click()
Dim str As String
For i = 1 To Len(Cells(1, 1))
If Cells(1, 1).Characters(i, 1).Font.Strikethrough = False Then str = str + Cells(1, 1).Characters(i, 1).Text
Next i
Cells(10, 1) = str
End Sub

This will copy the phrase without the word "brown" to the cell (10,1)
But be alerted, this will leave the phrase with two spaces between the words "quick" and "fox" because the code deleted the word "brown" only, and left the spaces before and after it.

I shall leave the tuning of the code to remove the extra space for you :)

you can download the example here:

http://rapidshare.com/files/330791064/Book1.xls[^]

John242424 · Answer 2 · 2010-10-19T09:09:00

Hi Chaegie,
Not sure but I thought you were looking for a C# solution. Here is something quick and dirty. I'm sure there is a more eloquent solution but this works. And, at the very least, it shows the C# syntax to detect strike-through as a starting point for you.

using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;

...
...

//open some workbook and get a spreadsheet...
Excel.Application app = new Excel.Application();

Excel.Workbook workbook = app.Workbooks.Open("workbook name",
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing);

//get the worksheet from the workbook
Excel.Worksheet ws = (Excel.Worksheet)app.Sheets["worksheet name"];

...
...

//set a range equal to a single cell that has your text that needs cleaned
Excel.Range range = worksheet.get_Range("somecell", "somecell");

string clean_string = CleanStrikethroughChars(Excel.Range range);
//now you can replace the cell value with the clean_string value
...
...

string CleanStrikethroughChars(Excel.Range range)
{
string s="";
int char_index = 1;
int length = range.Cells.Value2.ToString().Length;

while(char_index<length)
{
//just keep building the string with any char that is not strikethrough
if(!(bool)range.Cells.get_Characters(char_index,1).Font.Strikethrough;)
s+=range.Cells.get_Characters(char_index,1).Text;

char_index++;
}
return s;
}

Identify strikethrough text in excel cell

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0