If statement with formatted text

  • Thread starter Thread starter Doug Glancy
  • Start date Start date
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
 
Not within a formula.

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


Gord Dibben MS Excel MVP
 
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
 
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
 
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
 
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
 
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
 
Back
Top