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

 

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.

      • sunnyschindler's avatar
        sunnyschindler
        Brass Contributor
        Aug 03, 2023

        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

        '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 Sub

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

         

Resources