ATOCODES

ATOCODES(Array,Remove_Nonprinting)
Array
Required. Array to return individual character codes.
Remove_Nonprinting
Optional. 1☛ remove non-printing characters listed in CLEAN; 0 or ignored☛ don't remove

ATOCODES returns an array of individual character codes from a one dimensional array of text strings with an option to remove non-printing characters.

schardt679

Board Regular
Joined
Mar 27, 2021
Messages
58
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
ATOCODES returns an array of individual character codes 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.
Calls ATOCHARS.
Other functions on minisheet: AFORMULATEXT.

Excel Formula:
=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),
          UChars, ATOCHARS(Arr, RemNon?),
          UCodes, UNICODE(UChars),
          Result, IF((UChars="")+(ISERROR(UChars)), UChars, UCodes),
          Return, IF(ERRORS<>0, ERRORS, Result),
          Return
      )
 )
LAMBDA Examples.xlsx
ABCDEFGHIJKLMNOP
1ATOCODES
2
3TextResult
4 Hello 1607210110810811132323232
5
6There!8410410111410112733
7
8#N/A#N/A
9I am 27.7332971093232323232505546
10
11Data is a vertical array
12Result spills horizontally
13Nonprinting characters aren't removed
14Formula in cell D4☛ =ATOCODES(B4:B9,)
15
16
17
18TextResult
19 Hello 72101108108111
20There!8410410111410133
21
22#N/A#N/A
23I am 27.73329710932505546
24
25Data is a vertical array
26Result spills horizontally
27Nonprinting characters aren't removed
28Formula in cell D19☛ =ATOCODES(B19:B23, 1)
29
30
31
32Text Hello There!#N/AI am 27.
33
34Result16084#N/A73
357210432
3610110197
37108114109
3810810132
3911112732
40323332
413232
423232
433250
4455
4546
46
47Data is a horizontal array
48Result spills vertically
49Nonprinting characters aren't removed
50Formula in cell C34☛ =ATOCODES(C32:G32,)
51
ATOCODES
Cell Formulas
RangeFormula
D4:O9D4=ATOCODES(B4:B9,)
B14B14=AFORMULATEXT(D4)
D19:K23D19=ATOCODES(B19:B23, 1)
B28B28=AFORMULATEXT(D19)
F32F32=NA()
C34:G45C34=ATOCODES(C32:G32,)
B50B50=AFORMULATEXT(C34)
Dynamic array formulas.
 
Upvote 0
ATOCODES has been updated. Since the function calls ATOCHARS which 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.

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

Horizontal ranges spill down rows and vertical ranges spill across columns.
Calls ATOCHARS.
Other functions on minisheet: AFORMULATEXT.

Screen Tip/ Comment: =ATOCODES(Array☛ range to return individual character codes, [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: ATOCODES(Array,[Clean_Data],[Delimiter])

Array: Required. Array to return individual character codes.
[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.

Excel Formula:
=LAMBDA(Array,[Clean_Data],[Delimiter],
      LET(Arr, Array,          Clean?, Clean_Data,          Del, Delimiter,
             UChars, ATOCHARS(Arr, Clean?, Del),          UCodes, UNICODE(UChars),
             Result, IFERROR(UCodes,  UChars),          Return, IF(LEN(UChars)>1, UChars,  Result),
             Return
      )
  )
LAMBDA Examples.xlsx
ABCDEFGHIJKLMNOPQRS
54ATOCODES
55
56TextClean_Data:1Result
5703/22///21Delimiter:/4851475050475049
58#VALUE!#VALUE!
5903//22///214851475050475049
6003//23/21 485147505147504932
61
6203/23/214851475051475049
6303/24/215252505557
64
65Data is a vertical array --> result spills horizontally
66Nonprinting characters and extra delimiters are removed
67Delimiter is slash
68Formula in cell G57☛ =ATOCODES(B57:B63, E56, E57)
69
70
71
72TextClean_Data:1Result
731, , , 2, 3, , 4Delimiter:, 4944325044325144323252
74A;;B;C;;;D;65596659675968
75A1|A2|||B||C|654912465501246612467
76D||E|||F||||G||68124124691241241247012412471
77
78Data is a vertical array --> result spills horizontally
79Nonprinting characters and extra delimiters are removed
80There are multiple delimiters.
81Formula in cell G73☛ =ATOCODES(B73:B76, E72, E73:E76)
82
ATOCODES
Cell Formulas
RangeFormula
G57:O63G57=ATOCODES(B57:B63, E56, E57)
B58B58=1+"A"
B68B68=AFORMULATEXT(G57)
G73:Q76G73=ATOCODES(B73:B76, E72, E73:E76)
B81B81=AFORMULATEXT(G73)
Dynamic array formulas.
 

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top