If statement with formatted text

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 


0
nobodyhere (32)
10/1/2008 11:13:57 PM
excel 39879 articles. 2 followers. Follow

9 Replies
741 Views

Similar Articles

[PageSpeed] 20

Not possible to format fonts in formulas


-- 


Regards,


Peo Sjoblom



"Doug Glancy" <nobodyhere@replytogroup.com> wrote in message 
news:uXMQftBJJHA.1308@TK2MSFTNGP02.phx.gbl...
> 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
> 


0
terre081 (3244)
10/1/2008 11:38:33 PM
Not within a formula.

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


Gord Dibben  MS Excel MVP

On Wed, 1 Oct 2008 16:13:57 -0700, "Doug Glancy"
<nobodyhere@replytogroup.com> wrote:

>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 
>

0
Gord
10/1/2008 11:39:12 PM
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

"Peo Sjoblom" <terre08@mvps.org> wrote in message 
news:OUFmS7BJJHA.1156@TK2MSFTNGP04.phx.gbl...
> Not possible to format fonts in formulas
>
>
> -- 
>
>
> Regards,
>
>
> Peo Sjoblom
>
>
>
> "Doug Glancy" <nobodyhere@replytogroup.com> wrote in message 
> news:uXMQftBJJHA.1308@TK2MSFTNGP02.phx.gbl...
>> 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
>>
>
> 


0
nobodyhere (32)
10/1/2008 11:45:41 PM
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

"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:ge28e45srnp77hd3em4r9ktd56l1ss89ki@4ax.com...
> Not within a formula.
>
> Convert to values then you can select the text to subscript.
>
>
> Gord Dibben  MS Excel MVP
>
> On Wed, 1 Oct 2008 16:13:57 -0700, "Doug Glancy"
> <nobodyhere@replytogroup.com> wrote:
>
>>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
>>
> 


0
nobodyhere (32)
10/1/2008 11:53:37 PM
On Wed, 1 Oct 2008 16:13:57 -0700, "Doug Glancy" <nobodyhere@replytogroup.com>
wrote:

>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 
>


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
0
ronrosenfeld (3122)
10/2/2008 1:00:11 AM
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

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:ei68e4hvg1c3f9hmof5f00609b7jo5isrc@4ax.com...
> On Wed, 1 Oct 2008 16:13:57 -0700, "Doug Glancy" 
> <nobodyhere@replytogroup.com>
> wrote:
>
>>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
>>
>
>
> 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 


0
nobodyhere (32)
10/2/2008 1:13:43 AM
On Wed, 1 Oct 2008 18:13:43 -0700, "Doug Glancy" <nobodyhere@replytogroup.com>
wrote:

>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

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
0
ronrosenfeld (3122)
10/2/2008 1:20:45 AM
Ron,

Like I said, I also was able to do it with a picture that referred to one of 
two correctly formatted cells.  I wasn't able to use an if statement in the 
picture formula, but I was able to refer to a named formula that contained 
the if statement.

But then I'm stuck with a picture over a cell.

I thought I'd mention it again, since it seemed like it might be of 
interest.

Doug

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:4c88e45sgjp8lgddnpp9n9mkup7nrevr10@4ax.com...
> On Wed, 1 Oct 2008 18:13:43 -0700, "Doug Glancy" 
> <nobodyhere@replytogroup.com>
> wrote:
>
>>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
>
> 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 


0
nobodyhere (32)
10/2/2008 1:29:05 AM
Hi,

well, first even if it is not a formula, can't format a part of a cell if it 
contains numbers or dates or logicals, only if the cell contains a string.  

However, you could put code in place that responded to the Change event 
which would put the formula in the cell, then convert it to text, then format 
the portion of the text appropriately.  This would run anytime the cell or 
one of the cells that fed the formula changed. 

-- 
Thanks,
Shane Devenshire


"Doug Glancy" wrote:

> Ron,
> 
> Like I said, I also was able to do it with a picture that referred to one of 
> two correctly formatted cells.  I wasn't able to use an if statement in the 
> picture formula, but I was able to refer to a named formula that contained 
> the if statement.
> 
> But then I'm stuck with a picture over a cell.
> 
> I thought I'd mention it again, since it seemed like it might be of 
> interest.
> 
> Doug
> 
> "Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
> news:4c88e45sgjp8lgddnpp9n9mkup7nrevr10@4ax.com...
> > On Wed, 1 Oct 2008 18:13:43 -0700, "Doug Glancy" 
> > <nobodyhere@replytogroup.com>
> > wrote:
> >
> >>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
> >
> > 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 
> 
> 
> 
0
10/2/2008 2:51:02 PM
Reply:

Similar Artilces:

how to format :send picture with message: as a default
each time i sent a graphic i must go to the format and click "send picture with message." how do I make this a default so it is there permanently.I am running on a Windows XP and am using Outlook Express for my mail. LYNNY <LYNNY@discussions.microsoft.com> wrote: > each time i sent a graphic i must go to the format and click "send > picture with message." how do I make this a default so it is there > permanently.I am running on a Windows XP and am using Outlook Express > for my mail. Ask in an Outlook Express newsgroup. -- Brian Tillman ...

STATEMENT DOWNLOAD PROBLEMS #2
Hi, I am trying to download statements and keep coming up with an error which says that "A communication error has occurred. Please try or call again." I have tried deleting and recreating the OFX file extension but this hasn't helped. Does anyone have any ideas? Thanks Caz In microsoft.public.money, Cazzy123 wrote: >I am trying to download statements and keep coming up with an error which >says that "A communication error has occurred. Please try or call again." I >have tried deleting and recreating the OFX file extension but this hasn't >he...

FORMATING COLUMNS..... HELP
I need to format columns to allow only 7 characters and the rest of the data to go into column B -- JTEFUN "JTEFUN" <JTEFUN@discussions.microsoft.com> wrote in message news:6C5C7A99-E83F-430E-9576-6D1DB57B9311@microsoft.com... >I need to format columns to allow only 7 characters and the rest of the >data > to go into column B > -- > JTEFUN > If you mean that if a user types a lot of data into the one cell and that when they reach 7 characters the rest of the data is automatically inserted into the next cell, then I don't think you can do that....

SOP Statements
Hello, We are using GP v 9.0. I have just recently taken on the task of sending out customer statements. I find that the User Definable Form has a great layout that I would like to send to our customers. However, our company name and address does not print on the form even though the option is marked when selecting the statement. Any suggestions on how to get the company information to print on the SOP Statement - User Definable Form? Thank you, Kristy If you're talking about the statements printed using Tools>Routines>Sales>Statements, then you'll need to modify t...

how do i change or remove a signiture that is under outo text hea.
Someone please help i am using outlook 2003 and i am triyng to change a signiture that is under auto text. to get into it i open a message push insert then outo text then signiture. I want to change it. I have closed the untilted message gone to tools then options then mail format then signitures then remove all the signitures but the signiture under insert and outo text is still there See if this helps: http://office.microsoft.com/en-us/word/HP051860291033.aspx -- Bill R "nearly bald with frustration" <nearly bald with frustration@discussions.microsoft.com> wrote i...

BULK Conditional Formatting
I've read the posts on conditional formatting for cell colour based o another cell's value (eg. set the conditional formatting to "formul is" and then "=A1>0" and set the colour as red / blue / whatever...) however wondering if I can do this for an entire column withou individually changing the conditional formatting for each cell one b one (as there are over 400 rows). Basically I need a formula that reads the contents of column B for th particular row that is active. Can anyone help -- Rob Moyl ---------------------------------------------------------------...

Counting Texts
If cell A1 contains a paragraph of texts and if I want to count just letter "W"s (Upper or lower case), How can I achieve this? Example: A1 contains "How now brown cow" the formula should return "4". Thank you. Write a macro and use the VBA functions Instr(strName, "w") together with Split(strName, "w") and count the number of times that it finds "w" or "W" Chris "Keith" <Keith@discussions.microsoft.com> wrote in message news:CC60AA13-B569-4C55-BDC7-1B6295CDAFB9@microsoft.com... > If cell A1 co...

Emails missing text
We have about 80 users on our Exchange server. We have 2 users that have an issue with reading their email. It appears that when they open the email the right side of the email/text is gone. When I shadow their session I can see the entire email but they cannot. It is as if they right section of the text of the email was deleted. I'm sorry I'm not explaining this well. Any feedback is appreciated. Thanks ...

Export excel file to semicolon delimited text file
Hello all. We have the need to export a file from excel to a semicolon delimited text file. Is there a way to do this from Excel directly? If not, does anyone have any other suggestions? TIA for your help. If you change your regional settings to use the semicolon as your list separator, you can save as .csv and it should work. But this may affect other programs, too--since it's a windows setting. windows start button|settings|control panel|regional and language options| regional options tab|customize button near the bottom. On the other hand, if you don't want to fiddle with...

text size in reading pane
Where is the font selection / text size control for the Outlook 2003 reading pane (incoming emails)? I just bought a brand new dell with windows xp and all and I can't even increase the text size of incoming emails as I could with older versions of Outlook Express. I have been trying to figure this out for around 15 hours now and have come up empty. What kind of progress is this anyway???? A program rendered unable to accomplish such a simple practical thing like that!! Im very upset. My vision is poor. Unless I get an answer soon, I am considering a return of this unit to dell...

can't see text in any office files anymore
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel on my macbookpro: can't see text in any office files anymore (word, powerpoint, excel). only when using preview function from OSX. <br> what's wrong??? <br> do I have to re-install the all suite? any quick fix? ...

Format Problem
I have a report of a crosstab query. I formatted the left column of the report with Text Align set to Right. The report is chopping off the last character of the term, even if I set the Right Margin to 0.5". Doing so just puts space between the right edge of the border and the end of the chopped word I have played with all of the format settings in the properties with no success. If I set Text Align to Left the word is not chopped, but I would prefer Text Align set to right. Is there a way to fix this format issue with Text Align set to the Right? -- Message posted via http://www.acce...

Complicated IF statement
Afternoon everyone. Have been struggling to get a formula together for the following. I am being told due to its complexity that I will probably need to break it down into 2 sections but hoping someone can help me simplify things. I need to do the following A1 = 1 (although this can be 1,2,3,4,5) (This is my variable I am struggling with) B1 = €10000 (TARGET) C1 - €6500 (ACTUAL) Then I have a few boxes showing the following <70% between 70-99% >100% A5 = 1 B5 = 10% C5 = 15% D5 = 18% A6 = 2 ...

Conditional Statements and Time Format
Hello, I have a spreadsheet where I need to calculate a range of time in a cell and display a value in another cell. Example is in cell F2 I have a time displayed of 15:34, and in the calculation window it displays as 3:34:00 PM. In Cell F3 I want to display one of three things, "1st shift", "2nd shift" or "3rd shift". Is it possible to use the conditional statements to give me the value of "1st shift" when cell F2 is between >= 07:00 and < 15:00? I can't find anything that speaks to getting thee range information from time. Thanks, Brian ...

Help Creating A Formula To Copy and Paste Text
Hi everyone. Thanks in advance for any help you may have. I need to create a formula that searches for a specific word in a column then cut the word and paste it to another column. Of course this would be simple if it was the only word in the column, but there is other text that I would like to remain in the orginal column.. Thanks again, Mary You have a reply at your other thread. Mary wrote: > > Hi everyone. Thanks in advance for any help you may have. > > I need to create a formula that searches for a specific word in a > column then cut the word and paste it to ...

Rich Text formatting in Access 2007
In Access 2007, is it possible to add rich text formatting to text in a memo field (or mixed formatting within any text field). I have just upgraded to the new version of Access, and it appears that this feature still has not been added. Is this correct? (If so, why?) If it is not possible to format individual words within a field in Access, , is there a simple, free add-in that will enable Access to do this kind of basic formatting, such as adding italic, bold, and underlining? Thanks very much for any information you may have. Bob Rich Text format for Memos in: Tables - Look in t...

formatting cell in vlookup
HELP I have an excel spreadsheet that is e:mailed to me by my home office. I use the vlookup feature to find the value in cell b from the information in a Proble At times when the answer is N/A and I go to the e:mailed spreadsheet to see if that number is really not there, it is in the e;mailed spreadsheet. If I type over that number in the cell on the e;mailed spreadsheet, then the formula will work in the target spreadsheet, sometimes This e;mailed spreadsheet is at a shared location on a servier, and the items I fix in the e;mailed spreadsheet on my desk top, are not available in any ...

Footnote text cannot be entered without mouse click
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Normally adding a footnote number automatically adds that number to the bottom of the page. A blinking text entry indicator normally allows me to enter the footnote text without any additional step. <br><br>However, today I wanted to add a footnote number after a word, and although the corresponding number correctly appears at the bottom of the page, and the blinking text entry indicator also appears ready to allow entering footnote text, yet when I try to type, nothing is entered. I must resort to ...

Add numbers accross columns after stripping away text
I have the following data in a spreadsheet: A1 B1 C1 D1 E1 F1 4.5f 6f 3.5f 3f 7.25f I need to be able to add the numbers together to give me 24.25, i.e. strip the fs away. The numbers will always be less than 10 and the there will only ever be .25 or.5 or .75 after the number (I don't know whether that is significant?). If anyone can show me how to do this I would be very grateful. I have been messing around with MID and FIND to no avail and then started thinking that SUMPRODUCT might have to get involved but it all got a bit m...

Mottled Text in GIF Image
I'm trying to put a GIF image into my signature page. The image is a Static GIF Image that consists of a graphic and some text. When I go to write the email it appears correctly, however when I send the email the text portion is mottled. I am sending the email to myself and this still occurrs so it is the same monitor, computer, software etc. Anyone have any ideas on what is causing this. Should I covert this image to a jpg or some other format? Thanks in advance ...

Copy a formula formatted as Text In Excel
I have a UDF that returns the formula of a given cell in a text format. Is there anyway to have excel recognize that the result of the function is a formula and not text. Once I click on the cell, hit F2 to edit it and then enter, Excel realizes it's a formula and then calculates the value, Nope. The formula in the cell is going to be the UDF--not the what formula string looks like. You could add some more steps. Select the range (if more than one cell) Convert to values (edit|copy, edit|paste special|values) and finally edit|replace what: = (equal sign) with: = replace all And exce...

Impossibly Tiny Text
On rare occasions Excel 2003 (with all updates) screws up and will display just one sheet with impossibly tiny text - like only one or two pels high on the screen. All other sheets continue to look normal. All the cells appear to still have correct information in them, it's just tiny. I can select the entire sheet and tell it to use 100 point type and it will produce type that looks like maybe 10 point type on the other sheets. Or I can copy an entire good sheet, and "Paste Special" only the formats onto the bad sheet and get back the impossibly tiny text again. Saving ...

Extracting a word from a long text field
I have a text field (up to 200 characters) and I need to extract a specific word. The text field name is "MEDICATIONS" and the word I need to extract which might appear anywhere in the field is 'VENOFER'. The word need to be entered in a separate field in a query. Could any one help as to how to enter the code in the query? Thanking you Leo In the criteria I entered Like "VENOFER" Or "Venofer" It seems to work. Will it cause any problems down the line? In other wrods is this an acceptable procedure? "Leo" wrote: >...

Downloading Statements #4
I have been using Money 2005 for a long time with no problems, however in the last couple of weeks when I download statements I get the message "You have no statements to read", but when I go to my individual accounts there are items to read. Why do I get this message? I have cleared my temp file and uninstalled/reinstalled Money. In microsoft.public.money, maltesebudgie wrote: >I have been using Money 2005 for a long time with no problems, however in the >last couple of weeks when I download statements I get the message "You have >no statements to read", ...

Largest text box
Hi there! I have 3 text boxes on my form and I need to find the largest number among the three. I tried =max([txt1],[txt2],txt3]) but this is obviously wrong. How can I do this? Thanks for all help offered! With Me If .txt 1 > .txt2 And .tx1 > txt3 Then 'Txt1 is greatest ElseIf .txt2 > .txt1 And .txt2 > .txt3 Then 'Txt2 is greatest Else 'txt3 is greatest End If End With -- Dave Hargis, Microsoft Access MVP "Johnny Bright" wrote: > Hi there! > > I have 3 text boxes ...