LOOKUP to include formatting with its Return Value? Formatting Code Function? non-VBA request

Brass Contributor

Without VBA, is it possible for LOOKUP to return formatting of its Return Value?

It is much easier if Formatting Code Function (similar to CODE function) can be exposed to end user.


I need to differentiate LOOKUP output from other formulae outputs within long formulae.

21 Replies


Afraid not. Formulae in Excel work with values, not with properties which are applied by formatting.

Thanks, Is there VBA already done for such?


Perhaps @Hans Vogelaar could clarify about VBA, that's not my territory.


It'd be complicated. Could you provide an example of what you want to do?

@sunnyschindler If your formatting needs are limited to find out which part of a long formula has returned the result, it may be possible to convert the lookup result to text. ie, if the result is to be numeric. Apply TEXT function around LOOKUP.


You may then switch between "developer mode" to user mode by right adjusting the result column and have all values look the same but if you need to use the result as input for further calculations, numbers as text will give you bad results.
This is a possibility but I do not recommend it because of possible calculation erorrs further on.


I'ld rather use a cell with 1 or 0 to switch between visualising what formula part the result comes from.



Above usable in classic Excel vesions.

If 365 the formula may handle both alfa and numeric replies without making the formula even longer by repeating the lookup part (formula row 3)



If the need is for the end user to know, then I would recommend another approach by putting the lookup part in an own hidden column. Then you could also use conditional formatting and have most of the formatting possibilities.

It's also possible to use positive and negative results but _showing_ them as positives with some possibilities to switch font color if found by lookup (=negative numbers).

Here, the numbers are formatted as 0;[Color5]0 and color5 looks like blue.



More about Excel colors eg at http://dmcritchie.mvps.org/excel/colors.htm

@Hans Vogelaar 

I need to superscript an output of Excel formula to differentiate from other outputs of other formulae in same cell. Is there a non-VBA solution?

I need help to superscript first 3rd and subscript 4th argument




Example of above use is I am delegate for expense claiming for my Executive who is always on the road and has disorganized receipts (for good reason). I need to claim expense only once a month by company policy. To deal with missing receipts, I mobile texting my Executive because he/she has too many emails. Unfortunately, summary (not yet unconfigurable for filter) from Enterprise level Expense App is large Spreadsheet on a mobile screen and contains too many details; My workaround is reduction to a single line of text of items for mobile texting; because delineating items cost footprint in a string, I do away with delineator characters by applying subsequent word alternatively with Superscript and Subscript.


Since there is not yet Excel functionality, I wish Lookup can. As demo by screenshot, long text message (red) becomes much shorter (green) and use less mobile display footprint.


If there is a better way, I much appreciate the help

excel function superscript.jpg


. Until my Enterprise Expense App cater to mobile messaging interim information such as comments, I wait for more end-user to voice how they mobile text a summary in spreadsheet format. Is there more effective way?

Thanks, I learnt more today but should have mentioned that my need is to mobile text a string; by TEXTJOIN LOOKUP outputs.
The best I can come up with (w/o VBA) paste TEXTJOIN output into WORD APP to substitute "control characters" (which, similar to your suggestion, wrapped around each LOOUP output) with superscripts and subscripts depending according to "control characters". Appreciate your help!
Thanks for prompt response, instead of duplicating my info, there is later details in my reply to Hans Vogelaar ‎Jul 26 2023 07:55 PM.

mobile text a string;
what do you mean this?
can.you. upload a file rather than screenshot image and show your expected result?

how about html like below?


I fear that what you want is impossible. Excel does not support formatting part of the result of a formula.

If a cell contains a formula, formatting always applies to the entire cell.


So you'd have to replace the formula with its result, then convert individual characters to superscript. But that would require VBA. Also the formula would be lost, so changes in the source data would not be reflected in the cells that used to contain a formula.

You could come close using HTML but as stated, you need a command macro to make the conversion.


Column E is textjoined with html tags for sup and sub.

E1 is named htmlSource. All cells below are saved and reopened as html.

Pasted in F1, named htmltarget.

Sub FilterHTML()
    tmpFile = "_tmp.htm"
    Application.Goto "htmlSource"
    rowCount = Range("a1").SpecialCells(xlCellTypeLastCell).Row - Range("htmlSource").Row
    text = "<HTML>"                                 'begin with HTML tag
    For rowCounter = 0 To rowCount                  'loop through the cells
        str = Range("htmlSource").Offset(rowCounter, 0).Value
        If str = "" Then str = "&#00"               'nothing on empty rows
        text = text & str & "<BR>" & vbCrLf         'add line break
    Next rowCounter
    text = text & "</HTML>"                         'end with closing HTML tag
    path = ThisWorkbook.path & "\" & tmpFile        'get path to a temp file in same folder
    fileNo = FreeFile
    Open path For Output As #fileNo
    Print #fileNo, text                             'Write the selected data into the text file. 'Write' would include "
    Close #fileNo

    Application.Goto "htmlTarget"
    Range(ActiveCell, ActiveCell.Offset(Range("a1").SpecialCells(xlCellTypeLastCell).Row - ActiveCell.Row)).ClearContents
    Workbooks.Open path                             'fetch html data
    Range(ActiveCell, Range("a1").SpecialCells(xlCellTypeLastCell)).Copy
    Windows(tmpFile).Close False                    'dispose the temp file
    Kill path
End Sub



Thanks for very useful code. However, it is not working; Is it because Unicode is the problem? My string is ":question_mark:4⸍24:bed:️SOME:heavy_dollar_sign:549;:fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43;:heavy_dollar_sign:394:fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43;:question_mark:4⸍25:bed:️SOME:heavy_dollar_sign:549;:fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43;:question_mark:4⸍26:bed:️SOME:heavy_dollar_sign:2619;:heavy_dollar_sign:394:fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43;6⸍21:heavy_dollar_sign:1723🛧;:question_mark:7⸍10:bed:️FAIR_MAKATI:heavy_dollar_sign:1581;:fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43;:fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43:heavy_dollar_sign:84;:question_mark::fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43:heavy_dollar_sign:84:bed:️FAIR_MAKATI:heavy_dollar_sign:1721;:fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43:heavy_dollar_sign:84;:heavy_dollar_sign:543:fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43:heavy_dollar_sign:84;:question_mark::fork_and_knife_with_plate:️:heavy_multiplication_x::question_mark:🧾:heavy_dollar_sign:43:heavy_dollar_sign:84:heavy_dollar_sign:37:bed:️FAIR_MAKATI:heavy_dollar_sign:3972"


Reason for my suspecting Unicode as problem is because VBA local window is not showing Unicode for said string.


Thanks for helping!


Is it because Unicode

Well, yes - they have to be HTML coded,

I continued on your path using keywords in the input for the unichars and appended a lookup table with the HTML codes.


A lambda function in col G loops through the rows in [Name in text] and replaces the keywords with [HTML]. 

Thus, column N shows also unicharacters with formats available in html.


BTW: FILTERXML is already available in Excel. Maybe FILTERHTML could be in a future version.



I ran some tests with converting the strings using calls to other program libraries like Internet Explorer but found no big upside in doing so.

Attached an enhanced version ending with "_XL.xlsm" (like not using IE) where you also can have your calculations and the output on separate sheets.

Using html to format calculated results may in deed come handy.


because VBA local window is not showing Unicode for said string.

How about build or edit html page and copy and paste to excel

Word is capable of what I need by find and replace (1) all emoji with superscript and (2) ASCII alphabets with subscript.
With my limited VBA expertise, I am tempted to record Word macro to do above with input (hyperlinked) from Excel (formula output).
I like to know if anyone can share code that Find and Replace (1) all EMOJI with a fixed formatting (2) all Alphabets with another fixed formatting.
Otherwise, I will spend the time creating a wheel probably someone else already perfected.

when play with HTML,no any problem with EMOJI.

I guess bosinander has provide some ways to hanlde with it in Excel.

I mean it should be easier to handle it with Markdown or HTML rather than VBA.

@bosinander Thanks for helpful sharing of file. On the other hand, I am finding how to view its insides and stay within IT constraint.

For Enterprise users, how to view code/formlae without opening macro file shared by external user? 

@sunnyschindler You're welcome :) And good point about files with acitve content!

Users without Enterprise possibilities may analyse the macro as plain text (if withheld) before applying it. 

Such xlsx version attached with the macro code as plain text on a sheet. 





@bosinander Thanks for more help. But my VBA literacy is insufficient and needs to change line from 

    ' Path = ThisWorkbook.Path & " \ " & tmpFile      'get path to a temp file in same folder
    Path = TargetFolder & " \ " & tmpFile      'get path to a temp file in same folder

The Target Folder works for code as follows

Sub FreeFile_Example1()

 Dim Path As String
 Dim FileNumber As Integer

Dim TargetFolder As String
TargetFolder = "C:\Users\wongsunn\OneDrive - Schindler\Documents\scrap"

 Path = TargetFolder & "\Articles2019File 1.txt"
 FileNumber = FreeFile

 Open Path For Output As FileNumber

 Path = TargetFolder & "\Articles2019File 2.txt"
 FileNumber = FreeFile

 Open Path For Output As FileNumber

End Sub

Now I have to resolve error "Open Path For Output As #fileNo". 

vba LOOKUP to include formatting .jpg