Home | Quick Start | Documentation | Download | Support | Contribute

# Strings In AFC

### String Functions

##### Reference to other string
 A B C 2 Text =C2 Text

##### Numbers as returned by String valued outputs
 A B C 3 0 =C3 0.0 4 12345.6789 ... 12345.6789 5 1200000 ... 1200000.0 6 123456789 ... 1.23456789E8 7 0.1 ... 0.1

##### Booleans are treated as 0/1!
 A B C D 8 1 =C8 true Excel says: true 9 0 ... false Excel says: false 10 1 =(C10 = D10) 1.0 1.0 Excel says: true 11 0 ... 1.0 2.0 Excel says: false

##### Date format is not respected!
 A B C 12 38032 =C12 38032.0 Excel says: 38032.0

##### Value conversion with concatenation
 A B C D 14 Zero is 0 =(C14 & " is " & D14) Zero 0.0 15 Fraction is 12345.6789 ... Fraction 12345.6789 16 Exponent is 1200000 ... Exponent 1200000.0 17 Integer is 123456789 ... Integer 1.23456789E8 18 Percent is 0.1 ... Percent 0.1 19 Bool is 1 =("Bool is " & C19) true Excel says: Bool is TRUE 20 Bool is 0 ... false Excel says: Bool is FALSE 21 Bool is 1 =("Bool is " & (C21 = D21)) 1.0 1.0 Excel says: Bool is TRUE 22 Bool is 0 ... 1.0 2.0 Excel says: Bool is FALSE

##### Excel converts date as number too!
 A B C 23 Date is 38032 =("Date is " & C23) 38032.0

##### Plain vs. scientific format with String valued outputs
 A B C 25 12 =D25 1.0 12.0 26 120 ... 2.0 120.0 27 1200 ... 3.0 1200.0 28 12000 ... 4.0 12000.0 29 120000 ... 5.0 120000.0 30 1200000 ... 6.0 1200000.0 31 12000000 ... 7.0 1.2E7 32 120000000 ... 8.0 1.2E8 33 1200000000 ... 9.0 1.2E9 34 12000000000 ... 10.0 1.2E10 35 120000000000 ... 11.0 1.2E11 36 1200000000000 ... 12.0 1.2E12 37 12000000000000 ... 13.0 1.2E13 38 120000000000000 ... 14.0 1.2E14 39 1200000000000000 ... 15.0 1.2E15 40 12000000000000000 ... 16.0 1.2E16 41 120000000000000000 ... 17.0 1.2E17 42 1200000000000000000 ... 18.0 1.2E18 43 12000000000000000000 ... 19.0 1.2E19 44 1.2E+20 ... 20.0 1.2E20 45 1.2E+21 ... 21.0 1.2E21 46 12340000000000000000 ... 18.0 1.234E19 47 1.234E+20 ... 19.0 1.234E20 48 -12340000000000000000 ... 18.0 -1.234E19 49 -1.234E+20 ... 19.0 -1.234E20

##### Plain vs. scientific format with &
 A B C D 51 Str 12 =("Str " & D51) 1.0 12.0 52 Str 120 ... 2.0 120.0 53 Str 1200 ... 3.0 1200.0 54 Str 12000 ... 4.0 12000.0 55 Str 120000 ... 5.0 120000.0 56 Str 1200000 ... 6.0 1200000.0 57 Str 12000000 ... 7.0 1.2E7 58 Str 120000000 ... 8.0 1.2E8 59 Str 1200000000 ... 9.0 1.2E9 60 Str 12000000000 ... 10.0 1.2E10 61 Str 120000000000 ... 11.0 1.2E11 62 Str 1200000000000 ... 12.0 1.2E12 63 Str 12000000000000 ... 13.0 1.2E13 64 Str 120000000000000 ... 14.0 1.2E14 65 Str 1200000000000000 ... 15.0 1.2E15 66 Str 12000000000000000 ... 16.0 1.2E16 67 Str 120000000000000000 ... 17.0 1.2E17 68 Str 1200000000000000000 ... 18.0 1.2E18 69 Str 12000000000000000000 ... 19.0 1.2E19 70 Str 1.2E+20 ... 20.0 1.2E20 71 Str 1.2E+21 ... 21.0 1.2E21 72 Str 12340000000000000000 ... 18.0 1.234E19 73 Str 1.234E+20 ... 19.0 1.234E20 74 Str -12340000000000000000 ... 18.0 -1.234E19 75 Str -1.234E+20 ... 19.0 -1.234E20 77 Zero is 0 =(C77 & " is " & D77) Zero 0.0 78 Fraction is 12345.6789 ... Fraction 12345.6789 79 Exponent is 12000000000 ... Exponent 1.2E10 80 Integer is 123456789 ... Integer 1.23456789E8 81 Percent is 0.1 ... Percent 0.1 82 Date is 38032 =("Date is " & C82) 38032.0 83 Bool is 1 =("Bool is " & C83) true Excel says: Bool is TRUE 84 Bool is 0 ... false Excel says: Bool is FALSE 85 Bool is 1 =("Bool is " & (C85 = D85)) 1.0 1.0 Excel says: Bool is TRUE 86 Bool is 0 ... 1.0 2.0 Excel says: Bool is FALSE

##### Plain vs. scientific format
 A B C 88 Str 12 =("Str " & D88) 1.0 12.0 89 Str 120 ... 2.0 120.0 90 Str 1200 ... 3.0 1200.0 91 Str 12000 ... 4.0 12000.0 92 Str 120000 ... 5.0 120000.0 93 Str 1200000 ... 6.0 1200000.0 94 Str 12000000 ... 7.0 1.2E7 95 Str 120000000 ... 8.0 1.2E8 96 Str 1200000000 ... 9.0 1.2E9 97 Str 12000000000 ... 10.0 1.2E10 98 Str 120000000000 ... 11.0 1.2E11 99 Str 1200000000000 ... 12.0 1.2E12 100 Str 12000000000000 ... 13.0 1.2E13 101 Str 120000000000000 ... 14.0 1.2E14 102 Str 1200000000000000 ... 15.0 1.2E15 103 Str 12000000000000000 ... 16.0 1.2E16 104 Str 120000000000000000 ... 17.0 1.2E17 105 Str 1200000000000000000 ... 18.0 1.2E18 106 Str 12000000000000000000 ... 19.0 1.2E19 107 Str 1.2E+20 ... 20.0 1.2E20 108 Str 1.2E+21 ... 21.0 1.2E21

##### Concatenation operator (&)
 A B C D E 2 Hello world! =(C2 & " " & D2) Hello world! 3 StartMiddleEnd =(C3 & D3 & E3) Start Middle End 4 StartEnd ... Start ' (empty string) End 5 MiddleEnd ... ' (empty string) Middle End 6 StartMiddle ... Start Middle ' (empty string)

##### CONCATENATE
 A B C D E 8 Hello world! =(C8 & " " & D8) Hello world! 9 StartMiddleEnd =(C9 & D9 & E9) Start Middle End 10 StartEnd ... Start ' (empty string) End 11 MiddleEnd ... ' (empty string) Middle End 12 StartMiddle ... Start Middle ' (empty string)

##### CLEAN
 A B C 14 text =CLEAN( C14 ) text 15 текст ... текст 16 text text ... text text 17 texttext ... text text 18 texttext ... text text 19 text ... text

##### LEN
 A B C D 21 5.0 =LEN( C21 ) Hello 22 5.0 ... world 23 0.0 ... ' (empty string) 24 5.0 ... 12.34 25 4.0 =LEN( (C25 & D25) ) ab cd

##### MID
 A B C D E 27 abc =MID( C27, D27, E27 ) abcdef 1.0 3.0 28 a ... abcdef 1.0 1.0 29 abcde ... abcdef 1.0 5.0 30 abcdef ... abcdef 1.0 6.0 31 abcdef ... abcdef 1.0 7.0 32 ... abcdef 1.0 0.0 33 b ... abcdef 2.0 1.0 34 bcde ... abcdef 2.0 4.0 35 bcdef ... abcdef 2.0 5.0 36 bcdef ... abcdef 2.0 6.0 37 ... abcdef 2.0 0.0 38 f ... abcdef 6.0 1.0 39 f ... abcdef 6.0 2.0 40 ... abcdef 6.0 0.0 41 ... abcdef 7.0 1.0 42 ... abcdef 7.0 0.0 43 ... ' (empty string) 1.0 2.0 44 !FE ... abcdef 0.0 7.0 Excel says: #VALUE! 45 !FE ... abcdef 1.0 -1.0 Excel says: #VALUE!

##### LEFT
 A B C D 47 a =LEFT( C47 ) abcdef 48 =LEFT( C48, D48 ) abcdef 0.0 49 a ... abcdef 1.0 50 ab ... abcdef 2.0 51 abcde ... abcdef 5.0 52 abcdef ... abcdef 6.0 53 abcdef ... abcdef 7.0 54 ... ' (empty string) 7.0 55 !FE ... abcdef -1.0 Excel says: #VALUE!

##### RIGHT
 A B C D 57 f =RIGHT( C57 ) abcdef 58 =RIGHT( C58, D58 ) abcdef 0.0 59 f ... abcdef 1.0 60 ef ... abcdef 2.0 61 bcdef ... abcdef 5.0 62 abcdef ... abcdef 6.0 63 abcdef ... abcdef 7.0 64 ... ' (empty string) 7.0 65 !FE ... abcdef -1.0 Excel says: #VALUE!

##### SUBSTITUTE
 A B C D E F 67 Hello World =SUBSTITUTE( C67, D67, E67 ) Hello world w W 68 Hello orld ... Hello world w ' (empty string) 69 Hello world ... Hello world ' (empty string) Z 70 Hella warld ... Hello world o a 71 Heppo world ... Hello world ll pp 72 Hello worlD! ... Hello world d D! 73 Hell wrld ... Hello world o ' (empty string) 74 Schmello world ... Hello world H Schm 75 Hello world ... Hello world l l 76 Heo word ... Hello world l ' (empty string) 77 Hello world ... Hello world q ' (empty string) 78 Hello world ... Hello world q z 79 Yes! ... Hello world Hello world Yes! 80 bAäÄàÀáÁ ... aAäÄàÀáÁ a b 81 Yes! ... Hello world Hello world Yes! 82 ... ' (empty string) 3.0 5.0 83 !FE =SUBSTITUTE( C83, D83, E83, F83 ) Hello world l _ 0.0 Excel says: #VALUE! 84 He_lo world ... Hello world l _ 1.0 85 Hel_o world ... Hello world l _ 2.0 86 Hello wor_d ... Hello world l _ 3.0 87 Hello world ... Hello world l _ 4.0

##### REPLACE
 A B C D E F 89 Ah! Hello world =REPLACE( C89, D89, E89, F89 ) Hello world 1.0 0.0 Ah! 90 Schmello world ... Hello world 1.0 1.0 Schm 91 Hello people ... Hello world 7.0 5.0 people 92 Hello people ... Hello world 7.0 10.0 people 93 Hello dear world ... Hello world 7.0 0.0 dear 94 Hello orld ... Hello world 7.0 1.0 ' (empty string) 95 Hello Vorld ... Hello world 7.0 1.0 V 96 Hello worldxx ... Hello world 50.0 1.0 xx 97 Hello worldxx ... Hello world 50.0 0.0 xx 98 new ... ' (empty string) 1.0 4.0 new 99 !FE ... Hello world 0.0 2.0 bla Excel says: #VALUE! 100 !FE ... Hello world 1.0 -1.0 bla Excel says: #VALUE!

##### EXACT
 A B C D 102 true =EXACT( C102, D102 ) ' (empty string) ' (empty string) 103 false ... Hello hello 104 true ... Hello Hello 105 false ... Hello HellO 106 false ... ' (empty string) a 107 false ... a ' (empty string) 108 false ... ' (empty string) 109 false ... ' (empty string) 110 false ... a ä 111 false ... a A 112 true ... 123 123.0 113 true ... 123.0 123

##### FIND
 A B C D E 115 2.0 =FIND( C115, D115 ) e Hello world 116 1.0 ... H Hello world 117 !FE ... X Hello world Excel says: #VALUE! 118 11.0 ... d Hello world 119 9.0 ... r Hello world 120 8.0 ... or Hello world 121 10.0 ... ld Hello world 122 1.0 ... Hello world Hello world 123 1.0 ... ' (empty string) Hello world 124 1.0 ... ' (empty string) ' (empty string) 125 !FE ... x ' (empty string) Excel says: #VALUE! 126 5.0 ... a àÀáÁaAäÄ 127 6.0 ... A àÀáÁaAäÄ 128 7.0 ... ä àÀáÁaAäÄ 129 8.0 ... Ä àÀáÁaAäÄ 130 3.0 =FIND( C130, D130, E130 ) l Hello world 2.0 131 3.0 ... l Hello world 3.0 132 4.0 ... l Hello world 4.0 133 10.0 ... l Hello world 5.0 134 10.0 ... l Hello world 10.0 135 !FE ... l Hello world 11.0 Excel says: #VALUE! 136 11.0 ... d Hello world 11.0 137 !FE ... d Hello world 12.0 Excel says: #VALUE! 138 3.0 ... llo Hello world 1.0 139 3.0 ... llo Hello world 3.0 140 !FE ... llo Hello world 4.0 Excel says: #VALUE!

##### SEARCH
 A B C D E 142 2.0 =SEARCH( C142, D142 ) e Hello world 143 1.0 ... H Hello world 144 !FE ... X Hello world Excel says: #VALUE! 145 11.0 ... d Hello world 146 9.0 ... r Hello world 147 8.0 ... or Hello world 148 10.0 ... ld Hello world 149 1.0 ... Hello world Hello world 150 1.0 ... ' (empty string) Hello world 151 2.0 ... e*o Hello world 152 5.0 ... o*l Hello world 153 8.0 ... o?l Hello world 154 !FE ... o??l Hello world Excel says: #VALUE! 155 5.0 ... ~**~?*~~. Use * or ?, escape with ~. 156 !FE ... ~**~**~~. Use * or ?, escape with ~. Excel says: #VALUE! 157 7.0 ... ~o~r Use * or ?, escape with ~. 158 8.0 ... g ~ Trailing ~ 159 !FE ... ' (empty string) ' (empty string) Excel says: #VALUE! 160 !FE ... x ' (empty string) Excel says: #VALUE! 161 5.0 ... a àÀáÁaAäÄ 162 5.0 ... A àÀáÁaAäÄ 163 7.0 ... ä àÀáÁaAäÄ 164 7.0 ... Ä àÀáÁaAäÄ 165 5.0 ... в аАбБвВгГ 166 5.0 ... В аАбБвВгГ 167 3.0 =SEARCH( C167, D167, E167 ) l Hello world 2.0 168 3.0 ... l Hello world 3.0 169 4.0 ... l Hello world 4.0 170 10.0 ... l Hello world 5.0 171 10.0 ... l Hello world 10.0 172 !FE ... l Hello world 11.0 Excel says: #VALUE! 173 11.0 ... d Hello world 11.0 174 !FE ... d Hello world 12.0 Excel says: #VALUE! 175 3.0 ... llo Hello world 1.0 176 3.0 ... llo Hello world 3.0 177 !FE ... llo Hello world 4.0 Excel says: #VALUE! 178 2.0 ... e*o Hello world 1.0 179 2.0 ... e*o Hello world 2.0 180 !FE ... e*o Hello world 3.0 Excel says: #VALUE! 181 8.0 ... e*o Hello Hello 5.0

##### LOWER
 A B C 183 hello =LOWER( C183 ) HELLO 184 hello ... Hello 185 hello ... hellO 186 ààááaaää ... àÀáÁaAäÄ 187 ... ' (empty string)

##### UPPER
 A B C 189 HELLO =UPPER( C189 ) HELLO 190 HELLO ... Hello 191 HELLO ... hellO 192 ÀÀÁÁAAÄÄ ... àÀáÁaAäÄ 193 ... ' (empty string)

##### PROPER
 A B C 195 This Is A Title =PROPER( C195 ) this is a TITLE 196 2-Cent'S Worth ... 2-cent's worth 197 76Budget ... 76BudGet 198 Ààá Áaa Ää ... àÀá ÁaA äÄ 199 Ааб Бвв Ггд Д ... АаБ бВв ГгД д

##### REPT
 A B C D 201 *abc*abc*abc =REPT( C201, D201 ) *abc 3.0 202 àÀáÁaAäÄ ... àÀáÁaAäÄ 1.0 203 ... abc 0.0

##### TRIM
 A B C 205 aaa bb c =TRIM( C205 ) aaa bb c 206 aaa bb c ... aaa bb c

##### MATCH (ascending)
 A B C D E F 208 !NA =MATCH( C208, D208:F208 ) A B D F Excel says: #N/A 209 1.0 ... B B D F 210 1.0 ... C B D F 211 2.0 ... D B D F 212 2.0 ... E B D F 213 3.0 ... F B D F 214 3.0 ... G B D F 215 2.0 ... c A C E 216 2.0 ... ç A C E 217 2.0 ... CD B C D 218 1.0 ... BC B C D

##### MATCH (descending)
 A B C D E F 220 3.0 =MATCH( C220, D220:F220, (-1.0) ) A F D B 221 3.0 ... B F D B 222 2.0 ... C F D B 223 2.0 ... D F D B 224 1.0 ... E F D B 225 1.0 ... F F D B 226 !NA ... G F D B Excel says: #N/A 227 2.0 ... c E C A 228 1.0 ... ç E C A 229 1.0 ... CD D C B 230 2.0 ... BC D C B

##### MATCH (equality)
 A B C D E F 232 !NA =MATCH( C232, D232:F232, 0.0 ) A B C D Excel says: #N/A 233 1.0 ... B B C D 234 2.0 ... C B C D 235 3.0 ... D B C D 236 !NA ... E B C D Excel says: #N/A 237 2.0 ... c B C D 238 2.0 ... c B C C 239 !NA ... ç B C D Excel says: #N/A 240 3.0 ... ç B C ç 241 !NA ... CD B C D Excel says: #N/A 242 !NA ... BC B C D Excel says: #N/A 243 2.0 ... ?wo One Two Three 244 1.0 ... *o* One Two Three 245 3.0 ... ?hr* One Two Three

### String Comparisons

##### Code page not fully supported
 A B C D 2 true =(C2 < D2) a à 3 true ... à ä 4 false ... ä b Excel says: true

##### Case insensitive
 A B C D 6 true =(C6 < D6) A b 7 true ... a B

##### Strings are always greater than numbers
 A B C D 9 false =(C9 = D9) 10 10.0 10 false =(C10 < D10) 10 10.0 11 true =(C11 > D11) 10 10.0 12 false =(C12 = D12) 10.0 10 13 true =(C13 < D13) 10.0 10 14 false =(C14 > D14) 10.0 10 15 true ... 10 10.0

##### Equal (=)
 A B C D 17 false =(C17 = D17) a b 18 false ... d c 19 true ... e e 20 false ... A AA 21 true ... AA AA 22 false ... AA A 23 false ... AA AB 24 false ... AB AA 25 false ... ' (empty string) A 26 false ... A ' (empty string) 27 true ... A a

##### Not equal (<>)
 A B C D 29 true =(C29 <> D29) a b 30 true ... d c 31 false ... e e 32 true ... A AA 33 false ... AA AA 34 true ... AA A 35 true ... AA AB 36 true ... AB AA 37 true ... ' (empty string) A 38 true ... A ' (empty string) 39 false ... A a

##### Greater than (>)
 A B C D 41 false =(C41 > D41) a b 42 true ... d c 43 false ... e e 44 false ... A AA 45 false ... AA AA 46 true ... AA A 47 false ... AA AB 48 true ... AB AA 49 false ... ' (empty string) A 50 true ... A ' (empty string) 51 false ... A a

##### Less than (<)
 A B C D 53 true =(C53 < D53) a b 54 false ... d c 55 false ... e e 56 true ... A AA 57 false ... AA AA 58 false ... AA A 59 true ... AA AB 60 false ... AB AA 61 true ... ' (empty string) A 62 false ... A ' (empty string) 63 false ... A a

##### Greater or equal (>=)
 A B C D 65 false =(C65 >= D65) a b 66 true ... d c 67 true ... e e 68 false ... A AA 69 true ... AA AA 70 true ... AA A 71 false ... AA AB 72 true ... AB AA 73 false ... ' (empty string) A 74 true ... A ' (empty string) 75 true ... A a

##### Less or equal (<=)
 A B C D 77 true =(C77 <= D77) a b 78 false ... d c 79 true ... e e 80 true ... A AA 81 true ... AA AA 82 false ... AA A 83 true ... AA AB 84 false ... AB AA 85 true ... ' (empty string) A 86 false ... A ' (empty string) 87 true ... A a