ARRAYFORMULA && Extract Num From String

0
votes

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

1
google-sheetsgoogle-sheets-formula
Not sure what you want. In your data you have the expected result where you have an array formula. What is wrong with that formula? - ZygD

1 Answers

1
votes

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