If font is strikethrough...

[dl7631]

Hi!
I have a string entry in A1 that is in strikethrough font. In B1, I'd like to write a formula - something like:
If (effect in A1 = strikethrough, 1, 0)
Is it possible? What is the correct formula?
Thanks a lot!

 

[sahild05]

use a custom function:

Function ist(r1 As Range) As Boolean
ist = r1.Font.Strikethrough
End Function

the function can then be used as =IST(a1)

 

[jasonb75]

Create a named range, call it Strike

in the 'refrs to' box enter =GET.CELL(23,INDIRECT("RC[-1]",0))

Then use the formula

=IF(Strike,1,0)

Note that this assumes the cell with the strikethru text will be to the immediate left of the formula.

 

[dl7631]

Thanks a lot!

 

[jasonb75]

If you're going with the UDF I would advise a small change.

Function ist(r1 As Range) As Boolean
Application.Volatile
ist = r1.Font.Strikethrough
End Function

 

[dl7631]

If you're going with the UDF I would advise a small change.

Function ist(r1 As Range) As Boolean
Application.Volatile
ist = r1.Font.Strikethrough
End Function

wow, thanks!

 

[Scott Huish]

The op wants to return a 1 or a 0:

Function ist(r1 As Range) As Byte
Application.Volatile
ist = abs(r1.Font.Strikethrough)
End Function

Hit F9 to recalculate if you change the strikethrough.

Excel Workbook
	A	B
1	1	1
Sheet1

 

[jasonb75]

The op wants to return a 1 or a 0:

You sound a touch over-sure of yourself there,

I'd like to write a formula - something like: If (effect in A1 = strikethrough, 1, 0)

That looks more like a logical test being required, with 1 and 0 shown for results to keep the example simple, something which the UDF provided by sahild05 was adequate for, it just needed to be made volatile to allow it to recalculate.

 

[Scott Huish]

I'm just saying that if the op wants to return a 0 or 1 as per the example given, you could write the UDF to do exactly that, rather than embed it in an IF function.

I'm not over sure of myself that's the example the op gave. Are you psychic? How could you possibly know that the sample formula is not what the op wants?

 

[jasonb75]

I'm just saying that if the op wants to return a 0 or 1 as per the example given, you could write the UDF to do exactly that, rather than embed it in an IF function.

My apologies hotpepper, but the opening line of your first reply came out as an adamant statement that the OP definitely wanted to return a 1 or 0 result, rather than a suggestion to a direct alternative if it was suitable.

Are you psychic?

I thought that was a mandatory requirement