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

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

• You can use the simpler =JOIN(", ",ArrayFormula(DECIMAL(SPLIT(REGEXREPLACE(Q22,"\^|:\d", ""),", "),36))) Nov 19, 2020 at 15:55

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))

• Thank you! This worked, I just added a join formula at the beginning to combine them back into a single cell Aug 1, 2019 at 15:54