Forum Discussion

sunnyschindler's avatar
sunnyschindler
Brass Contributor
Jul 25, 2023

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

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 di...
excel
Formulas and Functions
office 365
bosinander's avatar
bosinander
Steel Contributor
Jul 27, 2023

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 = "&#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
    ThisWorkbook.Activate
    ActiveSheet.Paste
    
    Windows(tmpFile).Close False                    'dispose the temp file
    Kill path
End Sub

 

  • sunnyschindler's avatar
    sunnyschindler
    Brass Contributor
    Jul 28, 2023

    bosinander 

    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!

     

    • peiyezhu's avatar
      peiyezhu
      Bronze Contributor
      Jul 30, 2023

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

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

      • sunnyschindler's avatar
        sunnyschindler
        Brass Contributor
        Jul 31, 2023
        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.
    • bosinander's avatar
      bosinander
      Steel Contributor
      Jul 29, 2023

      > 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.

Resources