0

I am trying to average some cells, but they contain both numbers and characters. Obviously excel reads these as text so doesn't give an average of the numbers.

What can I use to make the formula only look at the numbers part of the cell? I tried a combination of sum and left but this hasn't worked. Do I need an array formula perhaps (something involving ={sum(left(A1:C1))});

For example

Cell    A1   B1   C1

Grade  6a  8b  4c

the answer should be (6+8+4)/3=6

Becky
  • 13
  • 3
  • You are going to require a UDF or an overly complicated array formula. –  Sep 10 '17 at 20:04
  • You can use Regex to parse the variables properly and then use the new data as desired. The example here shows in detail, but the opposite, they keep the letters. https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops – Reeza Sep 10 '17 at 20:22

1 Answers1

1

If there is always one and only one letter after the number you could create a second row that strips it off as follows:

 Cell A2:  =1*Mid(A1,1,Len(A1)-1)

Important to have the 1* part of this formula as then it creates a number. Then you can average this row.

Alternatively you could use the array formula:

=AVERAGE(1*MID(A1:C1,1,LEN(A1:C1)-1))

remember to hit CTRL+SHIFT ENTER when you are done editing the cell. Excel will put {} around the function in the editing window to show it's an array formula.

user1228123
  • 424
  • 4
  • 15
  • There will be multiple rows, so I would then need to split each row, I think this method could end up cumbersome? Or would that still work do you think? – Becky Sep 10 '17 at 20:08
  • Hi Becky, you could do it on a second sheet that references the first sheet. Alternatively, you could probably achieve the same effect with an array formula. A second alternative would be to write your own Macro function. – user1228123 Sep 10 '17 at 20:11