google sheets - Formula breaks when placed inside arrayformula

Question

Background: In the formulas below, I am trying to find any number that has the sequential pattern of +1. Example: 1011 (after 10, comes 11) =(FirstTwo+1=11)=Last 2.

Current Formulas:

• A1 =ARRAYFORMULA(ROW(1000:1011)) //List numbers from 1000 to 1011.
• B1 =ARRAYFORMULA(LEFT(A1:A,2)) //Only retrieve the first 2 digits.
• C1 =ARRAYFORMULA(RIGHT(A1:A,2)) //Only retrieve the last 2 digits.
• D1 =ARRAYFORMULA(if((B1:B12+1=C1:C12),TRUE,FALSE)) // If first 2 digits +1 = last 2 digits, TRUE.

Expected Outcome: D12 = TRUE

Actual Outcome: D12 = FALSE

My only found solution (not a good one)

1. Delete formula in C1.
2. Manually type each number in column C (resulting in C12 containing "11")
3. And now the array formula works for D1, and correctly applied TRUE in D12.

Why cant I achieve the same results using the formula in C1?

question from:https://stackoverflow.com/questions/65869984/formula-breaks-when-placed-inside-arrayformula

Answer 1

The problem is that the output data type is text and not an integer. You can test it by using the function type(). Reference document link.

You can fix it by multiplying the outputs by 1. Eg.:

B1: =ARRAYFORMULA(LEFT(A1:A,2)*1)

C1: =ARRAYFORMULA(RIGHT(A1:A,2)*1)