Formatting Remooval

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave B. - 20 Jul 2006 18:05 GMT

Hello all,
I am having an issue of special formats making lookups fail. On 2 excel
sheets with data I need to merge, via lookup, both have multiple corrections
on them. The problem lies in this, the corrections are done by using the
"Strike out" feature, which causes errors in the lookup. Is there any way,
or special syntax I cna use, possibly in the find and replace menu, to find
ALL data with strike outs and remove them?
Thanks
Dave B.

Reply to this Message

Scoops - 20 Jul 2006 18:12 GMT

> Hello all,
> I am having an issue of special formats making lookups fail. On 2 excel
[quoted text clipped - 5 lines]
> Thanks
> Dave B.

Hi Dave B

Not sure what "strike out" is but if you mean Strikethrough, then that
should make no difference to your lookup, the value in the cell has not
been altered.

Regards

Steve

Reply to this Message

Dave B. - 20 Jul 2006 18:25 GMT

Yes, strikethrough, that's what I meant. The entire sheet is formatted as
general, and I have done many of these lookups, and there isnt anything else
special or different on this sheet excent that all the correction, that dont
really need to be there anymroe, are red with a line through them. When I
try and run a basic lookup, it errors out, and the error is something to the
affect of odd or unrecognisable characters cause the lookup not to work.
Any ideas?
Also, the data in the strikethrough parts dont need to be in the sheet
anymore anyway, so can they be globally removed form the sheet?
Thanks
Dave

> > Hello all,
> > I am having an issue of special formats making lookups fail. On 2 excel
[quoted text clipped - 15 lines]
>
> Steve

Reply to this Message

Gord Dibben - 20 Jul 2006 19:08 GMT

CTRL + a(twice in 2003) to select all cells.

Format>Cells>Font. Uncheck the "strikethrough" option to remove the formatting.

I agree with Steve that the formatting will not cause errors in your lookups.

To remove all the data that has been formatted with the "strikethrough" would
require VBA or manual removal.

Sub strike3()
Dim ocell As Range
For Each ocell In Selection
If ocell.Font.Strikethrough = True Then
ocell.Clear
End If
Next ocell
End Sub

Gord Dibben MS Excel MVP

>Yes, strikethrough, that's what I meant. The entire sheet is formatted as
>general, and I have done many of these lookups, and there isnt anything else
[quoted text clipped - 27 lines]
>>
>> Steve

Reply to this Message

Duke Carey - 20 Jul 2006 20:13 GMT

You could also do a Find All, looking for cells that are formatted with the
strikethrough font attribute, then select all from the Find box, activate
Excel (de-activate the Find dialog), and delete.

> CTRL + a(twice in 2003) to select all cells.
>
[quoted text clipped - 47 lines]
> >>
> >> Steve

Reply to this Message

Gord Dibben - 20 Jul 2006 21:30 GMT

Duke

Good point to use Find and Replace.......if OP is running 2002 or newer.

I forget the newer versions have the "format" feature.

Gord

>You could also do a Find All, looking for cells that are formatted with the
>strikethrough font attribute, then select all from the Find box, activate
[quoted text clipped - 51 lines]
>> >>
>> >> Steve

Reply to this Message