ATOCHARS

[schardt679]

ATOCHARS returns an array of individual characters from a one dimensional array of text strings with an option to remove non-printing characters.
Horizontal ranges spill down rows and vertical ranges spill across columns. Thanks to RicoS and their TEXTTOARRAY function for the inspiration. Calls AONEDIM & ATRIM.

=LAMBDA(Array,Remove_Nonprinting,
      LET(Arr, Array,
          RemNon?, Remove_Nonprinting,
          A1D?, AONEDIM(Arr),
          AScan, AND(A1D?<>{"H","V"}),
          Horiz?, A1D?="H",
          NonScan, AND(RemNon?<>{1,0}),
          NonMSG, "Nonprinting=1,0",
          ERRORS, IFS(AScan, A1D?,  NonScan, NonMSG,  1, 0),
          ArrTrim, IF(RemNon?=1, ATRIM(Arr,), Arr),
          Lengths, IFERROR(LEN(ArrTrim), 1),
          MaxLen, MAX(Lengths, 1),
          Start, SEQUENCE(IF(Horiz?, MaxLen, 1),  IF(Horiz?, 1, MaxLen)),
          Result, IF(Start>Lengths, "", MID(ArrTrim, Start, 1)),
          Return, IF(ERRORS<>0, ERRORS, Result),
          Return
      )
 )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		ATOCHARS
2
3		Text		Result
4		Hello			H	e	l	l	o
5		There!		T	h	e	r	e		!
6
7		#N/A		#N/A
8		I am 27.		I		a	m						2	7	.
9
10		Data is a vertical array
11		Result spills horizontally
12		Nonprinting characters aren't removed
13		=ATOCHARS(B4:B8,)
14
15
16
17		Text		Result
18		Hello		H	e	l	l	o
19		There!		T	h	e	r	e	!
20
21		#N/A		#N/A
22		I am 27.		I		a	m		2	7	.
23
24		Data is a vertical array
25		Result spills horizontally
26		Nonprinting characters are removed
27		=ATOCHARS(B18:B22, 1)
28
29
30
31		Text	Hello	There!		#N/A	I am 27.
32
33		Result		T		#N/A	I
34			H	h
35			e	e			a
36			l	r			m
37			l	e
38			o
39				!
40
41
42							2
43							7
44							.
45
46		Data is a horizontal array
47		Result spills vertically
48		Nonprinting characters aren't removed
49		=ATOCHARS(C31:G31,)
50
ATOCHARS

Cell Formulas
Range	Formula
D4:O8	D4	=ATOCHARS(B4:B8,)
B7,F31,B21	B7	=NA()
B13,B27	B13	=FORMULATEXT(D4)
D18:K22	D18	=ATOCHARS(B18:B22, 1)
C33:G44	C33	=ATOCHARS(C31:G31,)
B49	B49	=FORMULATEXT(C33)
Dynamic array formulas.

 

[schardt679]

ATOCHARS has been updated. Since the function calls ATRIM (removes extra occurrences of a specified delimiter and nonprinting characters listed in CLEAN), an optional Delimiter argument was added. By default, it is a space.

ATOCHARS returns an array of individual characters from a one dimensional array of text strings with an option to remove extra occurrences of a specified delimiter and non-printing characters.

Thanks to RicoS's TEXTTOARRAY function for the inspiration.
Horizontal ranges spill down rows and vertical ranges spill across columns.
Calls AONEDIM, IFBLANK, & ATRIM.
Other functions on minisheet: AFORMULATEXT.

Screen Tip/ Comment: =ATOCHARS(Array☛ range to return individual characters, [Clean_Data]☛ 1=remove extra delimiters and non-printing characters listed in CLEAN; 0/=don't, [Delimiter]☛ separator; =" ") ⁂[]= optional; =default; =omit

The syntax is slightly different: ATOCHARS(Array,[Clean_Data],[Delimiter])

Array: Required. Array to return individual characters.
[Remove_Nonprinting] is now [Clean_Data]: Optional. 1☛ remove extra delimiters and non-printing characters listed in CLEAN; 0 or ignored☛ don't remove
[Delimiter]: Optional. Value separator(s); ignored☛ space. Can be multiple separators but the row/ column count must match array.

If {Clean_Data] is omitted [Delimiter] is irrelevant

=LAMBDA(Array,[Clean_Data],[Delimiter],
      LET(Arr, Array,          Clean?, IFERROR(--(Clean_Data), 2),          Del, IFBLANK(Delimiter, " "),
             A1D?, AONEDIM(Arr),          AScan, AND(A1D?<>{"H","V"}),          H?, A1D?="H",
             CleanScan, AND(Clean?<>{1,0}),          CleanMSG, "Clean_Data=1,0",
             ERRORS, IFS(AScan, A1D?,  CleanScan, CleanMSG,  1, 0),
             Trim?, IF(Clean?, ATRIM(Arr, Del),  Arr),          Lengths, IFERROR(LEN(Trim?), 1),
             MaxLen, MAX(Lengths, 1),          Start, SEQUENCE(IF(H?, MaxLen, 1),  IF(H?, 1, MaxLen)),
             Result, IF(Start>Lengths, "",  MID(Trim?, Start, 1)),          Return, IF(ERRORS<>0, ERRORS, Result),
             Return
      )
  )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
54		ATOCHARS
55
56		Text		Clean_Data:	1		Result
57		03/22///21		Delimiter:	/		0	3	/	2	2	/	2	1
58		#VALUE!					#VALUE!
59		03//22///21					0	3	/	2	2	/	2	1
60		03//23/21					0	3	/	2	3	/	2	1
61
62		03/23/21					0	3	/	2	3	/	2	1
63		03/24/21					4	4	2	7	9
64
65		Data is a vertical array --> result spills horizontally
66		Nonprinting characters and extra delimiters are removed
67		Delimiter is slash
68		Formula in cell G57☛ =ATOCHARS(B57:B63, E56, E57)
69
70
71
72		Text		Clean_Data:	1		Result
73		1, , , 2, 3, , 4		Delimiter:	,		1	,		2	,		3	,			4
74		A;;B;C;;;D			;		A	;	B	;	C	;	D
75		A1|A2|||B||C			|		A	1	|	A	2	|	B	|	C
76		D||E|||F||||G			||		D	|	|	E	|	|	|	F	|	|	G
77
78		Data is a vertical array --> result spills horizontally
79		Nonprinting characters and extra delimiters are removed
80		There are multiple delimiters.
81		Formula in cell G73☛ =ATOCHARS(B73:B76, E72, E73:E76)
82
ATOCHARS

Cell Formulas
Range	Formula
G57:O63	G57	=ATOCHARS(B57:B63, E56, E57)
B58	B58	=1+"A"
B68	B68	=AFORMULATEXT(G57)
G73:Q76	G73	=ATOCHARS(B73:B76, E72, E73:E76)
B81	B81	=AFORMULATEXT(G73)
Dynamic array formulas.