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

Similar Articles

[PageSpeed] 32

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:

missing text 02-12-10
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop I can't see the some of the text in some of my incoming mail messages in entourage. Some messages are displayed correctly throughout, but some are not. I see the mail heading, but not all of the message text. This applies to both html messages and to plain text messages. If I hit reply (or reply all) I see all of the message text. <br><br>This problem was posted in December by another user, but no resolution was given. ...

Summing instances of text across worksheets
Hello, I have a workbook with separate sheets for each month. The sheets contain a list client names (entered exactly the same on each sheet). I need to summarize how many times a client name appears in total. For example, John Brown may appear Jan & Feb (=2) where Mary Brown may appear Jul, Sep, Dec (=3). I've been trying pivot tables without luck. Hoping someone can help. Thanks in advance! Never mind, I consolidated all the worksheet data into one and the pivot table works fine. Thanks anwyay! "George" wrote: > Hello, I have a workbook with separate sheets fo...

Find feature does not work with HTML messages (Content-type: text/html)
I have Outlook 2000 SP-3 running under Windows ME. When using the Find feature to locate all e-mails with a given word in the message, no items are found if the e-mails are of "text/html" content-type. The Find feature appears to work for any messages that have URLs embedded and are "multipart/alternative" or "text/plain" content-type. I noticed a similar post several months ago (see http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=%23qSBYIssDHA.2224%40TK2MSFTNGP09.phx.gbl). Is there a work-around for this problem, or should this be reported t...

Technique used to rotate text labels when creating a PDF file from Access
This might be of help to some people. I have been producing some PDF reports lately directly from Access using the basic techniques found here: http://groups.google.com/group/comp.databases.ms-access/msg/a49f0ddea9315902 The rotation technique I use below can only be used when Access creates the PDF file directly or when Access uses some PDF creation tool that allows the developer to insert additional PDF commands. I don't think that the Access 2007 PDF Add-in from Microsoft allows such customization. Even if you can find an appropriate place to put the PDF commands within...

too many different formatting cells
I can't open an excel document because whem I try to open it says that too many different formatting cells. How to resolve this and open this document? Maybe... XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/default.aspx?scid=213904 A few people have said that OpenOffice.Org has been able to open the file. Then they clean it up and save it there. Then excel can open that cleaned up version. http://www.openoffice.org, a 60-104 meg download or a CD jo wrote: > > I can't open an excel document because whem I try to open it says that too >...

Conditional Formatting #18
I am attempting to use the conditional formatting to color code some cells, I want to say, If C1 = 0 then highlight A1 in red, how would I do that. Thanks Michelle 1st: select "Formula Is" instead of "Cell Value Is" in the left dropdown. The rest of the line changes to one long textbox. Enter "=$C$1=0" (no quotes) then select your format style from "format" button and "color" dropdown. that should do it. >-----Original Message----- > >I am attempting to use the conditional formatting to color >code some cells, I wan...

Conditional formating #7
Need help figuring out a formula please. If "date entered" in cell exceedes todays date by 4 days, then format the cell background "Red". Thanks so much for any help you can give. -- Unplugged ------------------------------------------------------------------------ Unplugged's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36751 View this thread: http://www.excelforum.com/showthread.php?threadid=564685 Use a formula of =A2>=TODYA()+4 where A2 is assumed as the first cell with CF "Unplugged" <Unplugged.2bi5mq_1153833004...

conditional formating in Access
how to use Max funciton for conditional formating in Access form On Fri, 7 Mar 2008 01:43:00 -0800, sudyl wrote: > how to use Max funciton for conditional formating in Access form Max of What? Select the control. Click on Format + Conditional Formatting. Select Expression Is As the expression write: [ThisControlName] = Max([ThisControlName]) Select the colors wanted. Save the change -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail ...

Complicated conditional formatting
Here's a breakdown of what I'm trying to do...Employee scheduling worksheet that is easy to read and follow. There are 5 functions employees can have, each given a specific color via conditional formatting. Each column represents a period of time during the work day, each row a different employee. As it stands now, for the duration that the employee will work, I simply enter a 1, 2, 3, 4, or 5 corresponding to their function, and it will color each cell with that value. The problem I run into, is in a separate sheet, I'm wanting to use conditional formatting to apply the same c...

Printing odd pages in booklet format
I used to be able to do this easily in PageMaker, but I can't figure it out for the life of me in Publisher... I am creating a booklet. Each page is 8.5 w and 5.5 tall (ie, half-letter, landscape orientation). I'd like to print this booklet two pages to one peice of paper, so I can cut in the middle and punch for a ring binder. I'd like the target paper to be letter (8.5 x 11). However, when I try to set it up as a book, I get a weird page that's 17x5.5. How can I change how the page is set up, so I can get both 8.5 x 5.5 pages to print in the correct orientation on the...

How to keep display format when exporting XML?
First thank you for taking the time to read about my problem. My problem is this. When exporting my xml data, the time/date values loose their display format. A time of 01:30 am, for example, becomes something like 0.001353 when exported.. What do I need to do to keep the same format as is displayed in Excel? It would sure be great if you could help. Kind regards ...

How to convert US date format to European
My computer is set up to use the European style dates (ddmmyyyy), not the USA-style dates (mmddyyyy format). So if I enter "01/06/05" I get a date of 01-June-2005. However when I download some information in CSV format at times that same date is formatted like this: "06/01/05". However this is interpreted as 06-January-2005 in the spreadsheet. How can I convert the US formatted values to European, in effect swapping the first two numbers? Thanks T Meisner -- 1025 Osborne Rd., New Germany, NS, Canada B0R 1E0 Rename the .csv file to .txt File|open that .txt file...

Conditional Formatting 12-16-09
Hello, with a text entry containing 2 different fonts, like "?" (Font Symbol), then "Customers" (Font Arial), Conditional Formatting seems to apply only to the first font element (?) whereas the second element remains untouched by CF. Is there a workaround (so that the entry "? Customers" as a whole undergoes CF) ? Thank you in advance. H.G. Lamy ...

Date format of 0 and if statements
I have an is statment as such: =IF('Raw Data'!A5=0,,'Raw Data'!A5) This issue I have is when I format fields as "date" and the reference cell = 0 excel formates the cell as 01/00/00. I have also used the formula: =IF('Raw Data'!A5=0,"",'Raw Data'!A5) The issue I have is I want to create graphs of this data in a dynamic fashion, but when the data range of the graph encounters either "" or 1/00/00 as a date, it plots it as a data point. I need a way to make my IF statment to produce a BLANK or EMPTY cell. Is there a key word in...

2 Formatting Questions
Date Fields: Can we, in the CRM forms, format dates to read into the “15 Aug 2005” format? This is possible in reports, but by default, the forms use only numbers for dates, but in any format. Currency: Any ideas of how to track multiple currencies in the same application - specifically USD and Canadian? I know a Microsoft CRM installation currently supports one language. But aside from using a picklist to specify "USD", "CAN", "MEX", or other I'm concerned about how their forecasting. -- Carroll Little Vis.align, LLC 610-692-3290 x3326 www.visalign.c...

Text-To-Columns Fixed Width
When using Text-To-Columns, Excel "recognizes" that the data fits the Fixed Width criteria and PRE-assigns the width. In most of the cases I work with Excel is wrong 99% of the time. Is there a way to force Excel to NOT pre-assign the width (leave everything blank)? On the first panel of the wizard click Delimited, then on the second panel click space as the delimiter. Hope this helps. Pete On Feb 4, 12:50=A0am, TP <T...@discussions.microsoft.com> wrote: > When using Text-To-Columns, Excel "recognizes" that the data fits the Fix= ed > Width ...

Need help extracting text from EDLs
I really am trying to learn this stuff, but I haven't programmed anything since my TRS-80 Basic days and I'm a bit overwhelmed. I want to extract text from Edit Decision Lists so that I have a list of shots used in a film. Here is an example of one event of an EDL: 002 TAPE004 V C 04:45:22:06 04:45:24:14 02:00:08:00 02:00:10:08 FROM CLIP NAME: 7C-4_B_ DLEDL: PATH: /raids/luc_1/bun/reel_02/oscans/1222/bun_7c_4_b_02/2048x1556/ The text that follows "FROM CLIP NAME:" is the name of the clip that I want to extract so that I have a document on onl...

Code to send SMS text message?
Hi, I would like to send an SMS text message to a cel phone via code within Excel 2007. Has anyone done this? What code / services / products do I need to accomplish this? Does anyone have an example? Thanks in advance. --Dan What gateway are you using? What you would need to do is to snd a internet message to a gateway provider which would then send the messae out on the wireless land. Most likely you are using a webpage interface from your PC to interface with the gateway. So opening up an internet explorer and goiong to an URL is probably the method to use. ...

Excel should default to autoformat for chart text boxes
Excel in (Office 2003 and as far as I can tell all previous versions of Excel) should default to autoformat for chart text boxes - it currently selects "none" for outline and background options ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.micr...

Automatically display set text based on users composition
Hi, im trying to do something really simple, trouble is i dont know what the feature's called to be able to search for tips on how to do it. Basically in outlook messages, when a user begins writing a sentence e.g. "in the terms of" i need a tag to pop up that allows the user to press enter and then the remainder of what they will want to type in will be inserted in, its a yellow tag that comes up above the words. i dont know where it needs to be created and enabled. Cheers, Rhys. ...

Conditional Formatting
Hello all, I have a spreadsheet with 79,000 cells of information in rows and columns. On a separate worksheet I have 5,000 cells of information. I would like any information in the first sheet that is in the second sheet to turn to red. For example. Sheet1, column A hellothere howareyou iamfine Sheet2, column A hellothere iamfine I would like the two strings in sheet one which appear in sheet 2 to turn red, leaving the middle string black. Is there a way to do a lookup to compare the cell value to a range using conditional formatting? Thanks for any help. Mxx -- murphyz -----...

Using IF for conditional formatting?
Need help with displaying blank cells when formulas are inside. The formulas would display a value if the contents of a source cell is filled in. I'm attempting to use in the formula cells(like B2): IF(A2="","",A2*5) If A2 is null, then display blank in B2, else calculate A2 * 5. Is this the best approach to keeping formula cells clear until data is entered? Thanks!!!! Yes -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "Barb" <anonymous@discussions.microsoft.com> wrote in message news:fd0201c4...

Text box and formatting?
I am working on class schedule and have one text box which spans a few pages. Pages are double columns. I have a header for each class and paragraph given for class description. On the last page, last column, one description appears at the top with empty space for the rest of the column. I would like to change the formatting of the text in the whole text-box so that most of the last column is used. I can select the text in the text box which expands over the few pages and play with the font size, but that would mean that all the text would be the same size. Is there a way that text...

Formatting Chart Category Labels
I'm creating a fairly complex chart - a spider graph "dashboard" report that addresses 4 major areas of an organization's performance - finance, quality, efficiency and customer satisfaction. Each category has 4-7 data elements, so that there are a total of 22 data elements on the chart. There will be 2 sections of the report, a chart showing YTD, LYTD and Goal values for the data elements and the graph itself. I envision this as a relatively easy way for management or board to get a snapshot of comparative key performance indicators. So far so good - input form, c...

Conditional formating #20
I need help with conditional formating. I need Row 30 to change colour when D30 is equal to a certain value. A the moment i can only get the one cell to change colour :rolleyes -- smith.james ----------------------------------------------------------------------- smith.james0's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1623 View this thread: http://www.excelforum.com/showthread.php?threadid=39022 Select the row. Enter: CF1: Formula is =$D30 = "certain value" Format1: Patterns/<changed color> In article <smith.james0.1ssb2d...