Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    characters w/ different fonts in a cell

    Hi,

    I searched the Internet and found several posts that say I can't do what I'd like.

    I have a formula that returns one of two strings. Within each string, I'd like the infinity symbol. The font used in this cell is Calibari. This font has a very anemic looking infinity symbol. I'd like the infinity char to use the Symbol font - much better looking infinity.

    Is there a way to have most of the text in Calibaria (or Arial or other text font) while including infinity as part of it (or even a separately concatenated single-character string) from the Symbol font.

    Non VBA.

    TIA

    Fred

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Can you show us the two strings please ?

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    flburg,

    I know you asked for a non-VBA way but as a last alternative......The only way I know how do it is through a macro that can be given a short cut (Developer Tab> Macros> Options). In the following example, Ctrl-Shift-S will initiate a macro that looks for the infinity character in the string of the active cell and changes the font of that character to Symbol. The infinity character can be in any position in the string. This could be incorporated into a Worksheet change event to work only on specific cells when a value is inputted.

    To manually change just the infinity symbol, activate the cell and highlight the character in the formula bar. Right click it> format> select font> OK.

    HTH,
    Maud
    symbol4.png

    symbol3.png Symbol2.png

    Code:
    Public Sub Symbol()
    num = InStr(ActiveCell.Value, "~")
    If num = 0 Then Exit Sub
    With ActiveCell.Characters(start:=num, length:=1).Font
            .Name = "Symbol"
    End With
    End Sub
    Last edited by Maudibe; 2014-04-11 at 22:40.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    I believe you have the wrong symbol for infinity, should be an 8 on it's side. Unfortunately, none of the fonts in the Lounge support it!
    You can enter the symbol in Excel via Alt+0165.
    Then this mod to your code will get the proper symbol.
    Code:
    Option Explicit
    
    Public Sub Symbol()
    
       Dim iNum As Integer
       
       iNum = InStr(ActiveCell.Value, Chr(165))
       If iNum >= 0 Then
         ActiveCell.Characters(Start:=iNum, Length:=1).Font.Name = "Symbol"
       End If
       
    End Sub
    infinity.PNG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Tilde...infinity... what's the difference? Thanks for picking up on my brain fart!

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Maudibe View Post
    Tilde...infinity... what's the difference? Thanks for picking up on my brain fart!
    Maud, be careful with statements like that you'll have the Math Police after you! ROTFLOL.gif
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Reminds me of a quote from my favorite Pixar movie..... To tilde, and beyond! (Darth Vader, "One flew over the cookoo's nest", 2011)
    Oh, I'm so confused!

    flburg,

    If you have difficulty placing an infinity sign into a string, type HE*LLO into a cell then run the macro on the activecell. It will replace the Asterisk in any position with an infinity symbol in Symbol font. You can set shortcut keys described above.

    Symbol6.png Symbol7.png

    Code:
    Public Sub Symbol()
    num = InStr(ActiveCell.Value, "*")
    IF num = 0 then Exit Sub
    ActiveCell.Value = Replace(ActiveCell.Value, "*", ChrW(&H61))
    With ActiveCell.Characters(start:=num, length:=1).Font
            .Name = "Symbol"
    End With
    End Sub
    Last edited by Maudibe; 2014-04-12 at 01:44.

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Thanks Martin.

    As an example, consider the following formula:
    ="The range of the function is " & IF(a>0, "yv, " & infinity, "-" & infinity & ", yv")

    Instead of infinity, I want the single character that looks like a sideways 8 (or, as I tell my class, a lazy 8 resting on the job).

    All the other messages are, as I said, in the Calibari font. Not all fonts have the infinity character but the Calibari's infinity looks anemic so I wanted the Symbol font's infinity. However, for now, I printed this msg using the Arial font which has a decent infinity character - not much worse than the Symbol font version. Arial doesn't look too much different than Calibari for other characters, so probably no one would notice. I could even switch the font for all the other messages (maybe 15 or so msgs) to Arial but that is probably not worth it.

    So another solution might be to switch fonts for all the messages. Now the questions is no longer an Excel question: which character font (so not Symbol) has a good looking infinity?

    Fred
    Last edited by fburg; 2014-04-12 at 12:38.

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    When I said no VBA, I meant no VBA in the workbook that I make available.

    But no one is inputting an infinity symbol. As I mentioned in my response to Martin, the infinity symbol is part of an IF test based on other conditions. Can I use your code to change my formula, delete the code, and then the formula would be what I want - most of it in Calibari but the 1 character changed from Calibari's infinity to Symbol's infinity?

    I'm not sure I see that your Sub can do the above. But if I just fed the cell address where my formula is into a Sub like yours (using the .Contents attribute?) and had it search the string of the formula for infinity (not tilde) and do the change, could that work?

    Fred

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Maud - Just noticed your "manual" suggestion.

    At least in Excel 2010, the format menu item when right clicking the character is grayed out (as are most/all of the other useful formatting options).

    If you only have a string w/o a formula, you can do what you suggested - you can format as much as you want even with the drop down menus.

    Fred

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hey RG, Maud,

    Not that I'm a member of the Math Police but if you stretch the point, there isn't that much difference between tilde and infinity. Tilde is used in front of a number to mean approximately and since no one knows the exact value of infinity anyway, then tilde could mean approximately whatever number you wanted, even a very big one.

    Fred

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Per my response to your first VBA solution, does this work only for a text string or can it be placed inside a formula as I asked?

    Fred

  13. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    But no one is inputting an infinity symbol.
    Can it be done? Absolutely but I think you totally missed the answer. It is unfortunate that you feel VBA is not a solution when it is the only solution to your original post!

    Done with VBA
    symbol7.png

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Sym As Range
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Set Sym = Target.Offset(1, 0)
        If Target = "" Or WorksheetFunction.IsText(Target) Then
            Sym = ""
            Exit Sub
        ElseIf Target > 0 Then
            Sym = "yv, " & Chr(165)  'SAME AS ChrW(&HA5)
        Else:
            Sym = "-" & Chr(165) & ", yv"  'SAME AS ChrW(&HA5)
        End If
        num = InStr(Sym.Value, Chr(165))  'SAME AS ChrW(&HA5)
        If num = 0 Then Exit Sub
        With Sym.Characters(Start:=num, Length:=1).Font
           .Name = "Symbol"
        End With
    End If
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2014-04-13 at 10:23. Reason: post file

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    thanks Maud.

    I think I got the answer the first time around. Your original macro and/or the more recent one look to see if the infinity character code is embedded in the string of the target cell. If so, they change the font of that 1 character to use the Symbol font. The original code was just a sub while the more recent one was based on a worksheet change event checking the cell that caused the triggering of the event routine.

    What I was asking in my response to your original posting was whether I could run the code (or similar code) once to change the formula rather than running it everytime the cell changes to change the contents.

    While VBA might be the solution that appears to give me exactly what I want and there is no other way to do that, some environments where this would be run prohibit running macros. The ability to change that is not under my control.

    So I've decided to settle for the solution I posted of using a font for that msg that has a decent infinity symbol and the other characters are not that much different than Calibri. While I can see the difference, I don't think my audience (college math students) would notice this. Further, the message in question deals with the domain and range of a Polynomial function, so infinity is part of both. But many students have so much trouble even understanding the concepts of Domain and Range that a slightly different look to the font would probably be the last thing on their mind. However, the Calibri infinity symbol is so anemic that it might not even be recognized for what it is.

    Fred

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •