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