CODEWITHSUNDEEP
exceltextexcel-formulawingdings
Tuberose
Tuberose

Reputation: 444

Returning Wingdings characters within a text with an Excel formula

I want return appropriated Wingdings character at end of a string, with a formula as shown below:

enter image description here

Which formula can I write to return the shown Wingdings characters at the end of the regular string in a cell? (Instead of "?" character): Solid circle character for return in true situation of formula and hollow circle character return in false situation, so these two characters are in green range.

Upvotes: 1

Views: 5253

Answers (2)

ashleedawg
ashleedawg

Reputation: 21619

A formula alone cannot change the font of part of a cell.

The best you could do is have a VBA function to make the change as needed, which you could run manually, or perhaps assign to a button, timer, event, etc.

This sub changes the last character of the currently active cell to Wingdings:

Sub LastLettWingding_Active()
    'changes the font for the last character of the **active** cell
    ActiveCell.Characters(Start:=Len(ActiveCell), Length:=1).Font.Name = "Wingdings"
End Sub

...and this one does the same thing, but to the currently selected cell or range of cells:

Sub LastLettWingding_Selection()
    'changes the font for the last character of the all selected cells
    Dim c As Range
    For Each c In Application.Selection
        c.Characters(Start:=Len(c), Length:=1).Font.Name = "Wingdings"
    Next c
End Sub

Edit:

I didn't realize it's a formula result that you're trying to format part of.

Unfortunately, Neither a formula nor VBA can change the font of part of a formula result.

My only suggestion would be to automate a Copy + Paste Values to a different cell, change the partial-formatting of that cell with the function above, and perhaps "hide" the original (formula) cell.

Edit #2:

Oh yeah-- @TomSharpe's also made the think that Unicode symbols might be an easier solution (included in most fonts, instead of using a separate font without the alphabet).

✂ ✃ ✄ ✅ ✆ ✇ ✈ ✉ ✊ ✋ ✌ ✍ ✎ ✏ ✐ ✲ ✳ ✴ ✵ ✶ ✷ ✸ ✹ ✺ ✻ ✼ ✽ ✾ ✿ ❀ ❁ ❂ ❃ ❄ ❅ ❆ ❇ ❈ ❉ ❊ ❋ ❌ ➮ ➯ ➰ ➱ ➲ ➳ ➴ ➵ ➶ ➷ ➸ ⌚ ⌛ ❶ ❷ ❸ ❹ ❺ ● ◌ ○ ◊ ◦ □ ┘ ⌂ ⅕⃝ Ω ℗ Ө Ӂ Φ ʘ ʘ ① ② ③ ④ ⑤ etc.

Click an empty cell and then on the Insert tab, click Symbol. Make sure you're on a "regular" font (like Calibri) and scroll way down the list.

Double click the one(s) you want, then Insert.

Highlight the symbol now showing in the formula bar, copy from there, and now you can paste it into your IF formula.

(Or, just copy copy the symbols from above right into an Excel formula...)

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34180

You can get a fair approximation to it by using char(149) • / lower case o in one of the regular fonts

=IF(VALUE(RIGHT(C4,1))>=5,"Selected "&CHAR(149),"Selected o")

Upvotes: 0

Related Questions