Forum Discussion
sunnyschindler
Jul 25, 2023Brass Contributor
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 differentiate LOOKUP output from other formulae outputs within long formulae.
- bosinanderSteel Contributor
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
- sunnyschindlerBrass Contributor
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!
- peiyezhuBronze Contributor
because VBA local window is not showing Unicode for said string.
How about build or edit html page and copy and paste to excel
- bosinanderSteel Contributor
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
- sunnyschindlerBrass ContributorThanks, 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!- peiyezhuBronze Contributormobile 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?
https://answers.microsoft.com/en-us/msoffice/forum/all/how-do-i-highlight-cells-based-on-data-from/aeae4552-08f5-4742-92d6-4885298fa5bf
Afraid not. Formulae in Excel work with values, not with properties which are applied by formatting.
- sunnyschindlerBrass ContributorThanks, Is there VBA already done for such?
It'd be complicated. Could you provide an example of what you want to do?