If statement with formatted text

D

Doug Glancy

Hi,

Is there any way to have an if formula such as:

If (A1="Active", "KAE",KPE")

where the two letters after the K are formatted as subscript?

The best I've done is to paste a picture over the cell. The picture's
formula refers to named formula that selects one of two cells, the one with
correct string. One cell contains KAE and the other KPE with the subscript.
However, it means that I'm using a picture and it would be much nicer if I
could just do it in an If formula.

I hope that makes some kind of sense, and thanks in advance for your help.

Doug
 
G

Gord Dibben

Not within a formula.

Convert to values then you can select the text to subscript.


Gord Dibben MS Excel MVP
 
D

Doug Glancy

Peo,

Thanks. So I guess it's either what I mentioned or Change event code to
apply the subscript. Unless you have any other suggestions?

Doug
 
D

Doug Glancy

Thanks Gord,

I need the cell label to change according to another cells contents, so
can't just convert to value. I'll just do it some other way.

Doug
 
R

Ron Rosenfeld

Hi,

Is there any way to have an if formula such as:

If (A1="Active", "KAE",KPE")

where the two letters after the K are formatted as subscript?

The best I've done is to paste a picture over the cell. The picture's
formula refers to named formula that selects one of two cells, the one with
correct string. One cell contains KAE and the other KPE with the subscript.
However, it means that I'm using a picture and it would be much nicer if I
could just do it in an If formula.

I hope that makes some kind of sense, and thanks in advance for your help.

Doug
Click to expand...


You can use an event macro.
Assume the cell where you want to have Kae or Kpe is B1.
I am also assuming that the contents of A1 is manually entered, and not the
result of a formula. If it might be the result of a formula, then you will
need to include A1's precedents in the Target intersection statement.

Right click on the sheet tab and select View Code.
Paste the code below into the window that opens:

====================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B1")) Is Nothing Then
If Range("A1").Value = "Active" Then
Range("B1").Value = "KAE"
Else
Range("B1").Value = "KPE"
End If
Range("B1").Characters(2, 2).Font.Subscript = True
End If
End Sub
===============================



--ron
 
D

Doug Glancy

Thanks Ron,

I was thinking maybe I could avoid code, but I'm thinking it's probably the
way to go. (Although, I was pleased with my picture solution, since I
hadn't realized it could be done before this.

Doug
 
R

Ron Rosenfeld

Thanks Ron,

I was thinking maybe I could avoid code, but I'm thinking it's probably the
way to go. (Although, I was pleased with my picture solution, since I
hadn't realized it could be done before this.

Doug
Click to expand...

The only methods I know of to differentially format different characters in a
string of text, in Excel, only work if the text is actual characters. Since
one of your requirements is to tie this to a particular cell that changes
value, the formula has to be done within a VBA macro.

But glad I gave you some ideas.
--ron
 
Top