@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
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
=TEXTJOIN("",TRUE,Acronymn[@[🛧]:[:bed:️]])
USE CASE
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
. 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?
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"
ThisWorkbook.Activate
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 = "�" '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
ThisWorkbook.Activate
ActiveSheet.Paste
Windows(tmpFile).Close False 'dispose the temp file
Kill path
End Sub
> 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.
@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 folderThe Target Folder works for code as follows
'https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/freefile-function
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 SubNow I have to resolve error "Open Path For Output As #fileNo".
