Extracting single digit numbers from a cell with mixed text

Question

I have a set of cells that have single digit rating numbers mixed with text next to them (ratings of 1 - 9). What I'd like to do is for each of the cells, extract the numbers and produce an average. I know about functions like Search and RegexExtract, but I don't understand how to use them to get all the numbers as it seems to only return the first match.

Here is a sample of the text in a single cell:

A - 1
B - 5
C - 9
D -4
E -7
F -  2

For the above text, I'm looking to create an array {1,5,9,4,7,2} and pass that to the average function.

Also I'm using Google Sheets, but most of the standard Excel functions should work the same. Thanks.

Answer 1

If what you want is the AVERAGE of those values, in Sheets, try (with your data in A1:

=average(split(trim(REGEXREPLACE(A1,"\D+"," "))," "))

Instead of trying to extract each number, we merely remove the non-digits; then split and average them. It is sometimes a handy technique to remove what we don't want, rather than trying to extract what we do want.

For Excel users, if you have Excel 2016+ with the TEXTJOIN function, you can use this array formula:

=AVERAGE(ABS(FILTERXML("<t><s>" & SUBSTITUTE(TEXTJOIN(" ",TRUE,A1:A6)," ","</s><s>") & "</s></t>","//s[number()=number()]")))

Since this is an array formula, you need to "confirm" it by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula as observed in the formula bar

Answer 2

I would like to suggest, two steps solution will fix the issue:

caveat:

• Below shown method is for Excel users, since VBA doesn't works for Google Sheet.
• Excel file can be converted into XLSX file format which is suitable/editable with Google Sheet.
• SUMPRODUCT works with Google sheet also.

---

Step 1:

• Either press Alt+F11 or Right Click the Sheet TAB and from poped up menu hit View Code to get VB editor.

• Copy and Paste this code as Module, then return to the Sheet.

  Public Function SplitNum(pWorkRng As Range, pIsNumber As Boolean) As String
  
  Dim xLen As Long
  Dim xStr As String
  xLen = VBA.Len(pWorkRng.Value)
  For i = 1 To xLen
  xStr = VBA.Mid(pWorkRng.Value, i, 1)
  If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
      SplitNum = SplitNum + xStr
  End If
   Next
  End Function
  

• Enter this formul in Cell AI5: =SplitNum(AH1,TRUE)

Step 2:

• To get AVERAGE enter this formula in Cell AJ5.

  =SUMPRODUCT(--MID(AI5,ROW(INDIRECT("1:" & LEN(AI5))),1))/Len(AI5)
  

---

Google Sheet users can use this formula:

=AVERAGE(SPLIT(AH1,CONCATENATE(SPLIT(AH1,".0123456789"))))

---

N.B. - Adjust cell references in the formula as needed.