Custom If function to return an "Equation" or "Text Box"

[justinnally]

Hi all,

I have a particular problem that I need help with. I have a spreadsheet with numerous equations that have been either typed into a cell or created using "insert-->equation". A lot of these equations have many subscripts and superscripts. My problem is that I want a cell to return a specific equation based on a condition. If the condition is true I want the cell to return condition A, if not then condition B. When I tried using an IF or VLOOKUP function the equation that is returned is always without the subscript or superscript. Is there a User Defined Function that I can create to return an equation in a text box or some other way to return the equation formatted as it should be?

Thanks.

Justin

 

[pgc01]

Hi Justin
Welcome to the board

All the characters from a string returned by a formula have the same format, so you lose the subcript/superscript.

This means that in the general case a formula solution is a no go.

Remark (if only subscript/superscript digits):
If the subscript/superscript characters are only digits then you can use the unicode subcript/superscript digits. (also valid for some letters)

For ex.:
[TABLE="width: 90"]
<colgroup><col width="90"></colgroup><tbody>[TR]
[TD="width: 90"]5²+12²=13²
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 90"]
<colgroup><col width="90"></colgroup><tbody>[TR]
[TD="width: 90"][/TD]
[/TR]
</tbody>[/TABLE]
In this case I'm not using the character "2" with superscript format, I'm using the "²" character which is a different character that looks like a "2" superscript.

Also in the case of just digits you can use the characters directly in the text or build a udf that converts it.

I posted some time ago solutions for udf's that use these characters, but I can't find them now. If you want I can try to find them.

 

[Rhodie72]

Hi Justin
Welcome to the board

All the characters from a string returned by a formula have the same format, so you lose the subcript/superscript.

This means that in the general case a formula solution is a no go.

Remark (if only subscript/superscript digits):
If the subscript/superscript characters are only digits then you can use the unicode subcript/superscript digits. (also valid for some letters)

For ex.:
[TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]5²+12²=13²[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"][/TD]
[/TR]
</tbody>[/TABLE]
In this case I'm not using the character "2" with superscript format, I'm using the "²" character which is a different character that looks like a "2" superscript.

Also in the case of just digits you can use the characters directly in the text or build a udf that converts it.

I posted some time ago solutions for udf's that use these characters, but I can't find them now. If you want I can try to find them.

I believe they are part of the extended UNICODE subscript range which is beyond the first 255 characters on the standard map. I include the standard codes as an example. Some of them are in the primary range of the first 255 bytes

'#    Code     $    '#    Code     $    '#    Code     $    '#    Code     $    '#    Code     $    '#    Code     $
'-------------------------------------------------------------------------------------------------------------------
'1 to 31,129,141,143,144,157 Non Printing
'32   chr(32)[space]'33   chr(33)  !    '34   chr(34)  "    '35   chr(35)  #    '36   chr(36)  $    '37   chr(37)  %
'38   chr(38)  &    '39   chr(39)  '    '40   chr(40)  (    '41   chr(41)  )    '42   chr(42)  *    '43   chr(43)  +
'44   chr(44)  ,    '45   chr(45)  -    '46   chr(46)  .    '47   chr(47)  /    '48   chr(48)  0    '49   chr(49)  1
'50   chr(50)  2    '51   chr(51)  3    '52   chr(52)  4    '53   chr(53)  5    '54   chr(54)  6    '55   chr(55)  7
'56   chr(56)  8    '57   chr(57)  9    '58   chr(58)  :    '59   chr(59)  ;    '60   chr(60)  <    '61   chr(61)  =
'62   chr(62)  >    '63   chr(63)  ?    '64   chr(64)  @    '65   chr(65)  A    '66   chr(66)  B    '67   chr(67)  C
'68   chr(68)  D    '69   chr(69)  E    '70   chr(70)  F    '71   chr(71)  G    '72   chr(72)  H    '73   chr(73)  I
'74   chr(74)  J    '75   chr(75)  K    '76   chr(76)  L    '77   chr(77)  M    '78   chr(78)  N    '79   chr(79)  O
'80   chr(80)  P    '81   chr(81)  Q    '82   chr(82)  R    '83   chr(83)  S    '84   chr(84)  T    '85   chr(85)  U
'86   chr(86)  V    '87   chr(87)  W    '88   chr(88)  X    '89   chr(89)  Y    '90   chr(90)  Z    '91   chr(91)  [
'92   chr(92)  \    '93   chr(93)  ]    '94   chr(94)  ^    '95   chr(95)  _    '96   chr(96)  `    '97   chr(97)  a
'98   chr(98)  b    '99   chr(99)  c    '100 chr(100)  d    '101 chr(101)  e    '102 chr(102)  f    '103 chr(103)  g
'104 chr(104)  h    '105 chr(105)  i    '106 chr(106)  j    '107 chr(107)  k    '108 chr(108)  l    '109 chr(109)  m
'110 chr(110)  n    '111 chr(111)  o    '112 chr(112)  p    '113 chr(113)  q    '114 chr(114)  r    '115 chr(115)  s
'116 chr(116)  t    '117 chr(117)  u    '118 chr(118)  v    '119 chr(119)  w    '120 chr(120)  x    '121 chr(121)  y
'122 chr(122)  z    '123 chr(123)  {    '124 chr(124)  |    '125 chr(125)  }    '126 chr(126)  ~    '127 chr(127)  
'128 chr(128)  €    '130 chr(130)  ‚    '131 chr(131)  ƒ    '132 chr(132)  „    '133 chr(133)  …    '134 chr(134)  †
'135 chr(135)  ‡    '136 chr(136)  ˆ    '137 chr(137)  ‰    '138 chr(138)  Š    '139 chr(139)  ‹    '140 chr(140)  Œ
'142 chr(142)  Ž    '145 chr(145)  ‘    '146 chr(146)  ’    '147 chr(147)  “    '148 chr(148)  ”    '149 chr(149)  •
'150 chr(150)  –    '151 chr(151)  —    '152 chr(152)  ˜     '153 chr(153)  ™    '154 chr(154)  š    '155 chr(155)  ›
'156 chr(156)  œ    '158 chr(158)  ž    '159 chr(159)  Ÿ    '160 chr(160)       '161 chr(161)  ¡    '162 chr(162)  ¢
'163 chr(163)  £    '164 chr(164)  ¤    '165 chr(165)  ¥    '166 chr(166)  ¦    '167 chr(167)  §    '168 chr(168)  ¨
'169 chr(169)  ©    '170 chr(170)  ª    '171 chr(171)  «    '172 chr(172)  ¬    '173 chr(173)  *    '174 chr(174)  ®
'175 chr(175)  ¯    '176 chr(176)  °    '177 chr(177)  ±    '178 chr(178)  ²    '179 chr(179)  ³    '180 chr(180)  ´
'181 chr(181)  µ    '182 chr(182)  ¶    '183 chr(183)  ·    '184 chr(184)  ¸    '185 chr(185)  ¹    '186 chr(186)  º
'187 chr(187)  »    '188 chr(188)  ¼    '189 chr(189)  ½    '190 chr(190)  ¾    '191 chr(191)  ¿    '192 chr(192)  À
'193 chr(193)  Á    '194 chr(194)  Â    '195 chr(195)  Ã    '196 chr(196)  Ä    '197 chr(197)  Å    '198 chr(198)  Æ
'199 chr(199)  Ç    '200 chr(200)  È    '201 chr(201)  É    '202 chr(202)  Ê    '203 chr(203)  Ë    '204 chr(204)  Ì
'205 chr(205)  Í    '206 chr(206)  Î    '207 chr(207)  Ï    '208 chr(208)  Ð    '209 chr(209)  Ñ    '210 chr(210)  Ò
'211 chr(211)  Ó    '212 chr(212)  Ô    '213 chr(213)  Õ    '214 chr(214)  Ö    '215 chr(215)  ×    '216 chr(216)  Ø
'217 chr(217)  Ù    '218 chr(218)  Ú    '219 chr(219)  Û    '220 chr(220)  Ü    '221 chr(221)  Ý    '222 chr(222)  Þ
'223 chr(223)  ß    '224 chr(224)  à    '225 chr(225)  á    '226 chr(226)  â    '227 chr(227)  ã    '228 chr(228)  ä
'229 chr(229)  å    '230 chr(230)  æ    '231 chr(231)  ç    '232 chr(232)  è    '233 chr(233)  é    '234 chr(234)  ê
'235 chr(235)  ë    '236 chr(236)  ì    '237 chr(237)  í    '238 chr(238)  î    '239 chr(239)  ï    '240 chr(240)  ð
'241 chr(241)  ñ    '242 chr(242)  ò    '243 chr(243)  ó    '244 chr(244)  ô    '245 chr(245)  õ    '246 chr(246)  ö
'247 chr(247)  ÷    '248 chr(248)  ø    '249 chr(249)  ù    '250 chr(250)  ú    '251 chr(251)  û    '252 chr(252)  ü
'253 chr(253)  ý    '254 chr(254)  þ    '255 chr(255)  ÿ

 

[justinnally]

Hi PGC,

Thanks for the reply. Unfortunately it is not just numbers I need but letters too.
The sub/superscript could be any number or letter. Is there a way to create the unicode text I want and add it to the library of characters that exists already?

Thanks,

Justin