CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Brother Bear
Brother Bear

Reputation: 3

ARRAYFORMULA && Extract Num From String

Trying to use an arrayformula on google sheets to auto-populate a column which will extract the number from a string. I've found / made multiple methods of extracting the number from the string, but I can't seem to get it to work in an arrayformula.

This formula works perfectly to extract the text, but because it's creating an array it's not working within the array formula.

TEXTJOIN("",TRUE,IFERROR((MID(H13,ROW(INDIRECT("1:"&LEN(H13))),1)*1),""))

This below just won't work. I've tried multiple methods, but can't get it working. Clearly I'm making a circular reference, but I can't see to solve it.

=ARRAYFORMULA(IF(ROW(C12:C)=12, "Num", IF(ISBLANK(C12:C), "", TEXTJOIN("",TRUE,IFERROR((MID(H13:H,ROW(INDIRECT("1:"&LEN(H13:H))),1)*1),"")))))

Included is an example worksheet, with a column with expected output.

Thanks for your time! :)

Example Worksheet

Upvotes: 0

Views: 344

Answers (1)

JvdV
JvdV

Reputation: 75900

What you could try, as I demonstrated in M13 on your sheet, is to use REGEXREPLACE() to remove anything from your data that is not a digit through "\D":

=INDEX(IF(H13:H="","",REGEXREPLACE(TEXT(H13:H,"@"),"\D","")))

Upvotes: 1

Related Questions