How to convert strings separated by comma from base 36 to decimal

Question

I'm getting input from a google form. An associate scans a QR code giving a string like ^12t34:1

Cell B1 = ^12t34:1

The string between the ^ and : is base36. I can use

C1 =DECIMAL(SUBSTITUTE(REGEXREPLACE(B1,"((?:.*?,)(\^)|:[0-9][0-9]{0,1})", ""), "^", ""),36) and get 1810624

But associates can scan multiple entries in a single cell like Cell B1 = ^12345:1, ^54321:1, ^ph345:1, ^a4321:1

using the same formula I get a #NUM! error, so I used a split formula

=DECIMAL(Split(SUBSTITUTE(REGEXREPLACE(B3,"((?:.*?,)(\^)|:[0-9][0-9]{0,1})", ""), "^", ""),", "),36)

but it only returns the result for the first entry = 1776965

I'm trying to convert all entries from a single cell containing

^12345:1, ^54321:1, ^ph345:1, ^a4321:1

and keep them separated by a comma and space:

1776965, 8588665, 42787589, 16986745

Answer 1

Just put your formula inside ARRAYFORMULA function. The resulting formula is the following:

=ArrayFormula(DECIMAL(Split(SUBSTITUTE(REGEXREPLACE(B3,"((?:.*?,)(\^)|:[0-9][0-9]{0,1})", ""), "^", ""),", "),36))