How to average cells with numbers that also contain non-numeric characters?

[towabc]

I have a worksheet with numeric data, where some are preceded by non-numeric characters. I would like to average the all the values in the row, but the average function does not seem to consider the entries as numeric, so it didn't work.

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]<1.2
[/TD]
[TD]<2.2
[/TD]
[TD]3
[/TD]
[TD]<1.5
[/TD]
[TD]<2
[/TD]
[/TR]
</tbody>[/TABLE]

I tried using the mid function to remove the non-numeric character (e.g.:

mid(a1,2,4)[code], which worked, so it ended up as:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD] 
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1.2
[/TD]
[TD]2.2
[/TD]
[TD]3
[/TD]
[TD]1.5
[/TD]
[TD]2
[/TD]
[TD]#DIV/0
[/TD]
[/TR]
</tbody>[/TABLE]

 However when I tried  to average the results of that row I got the #DIV/0!. Cell E2 was [code]average(a1:d1)[code].  Is there any way for me to average these values without having to re-enter all the data manually?? I have much more data than shown here.

Any help would be useful! Thanks.

 

[Drrellik]

If the entered values look like <1.2 and >2.2 then they may not be numbers but rather General or text in your MID formula try MID(A1,2,4)*1 to convert to a number.

you can somethimes tell if a number is stored as text if it is left justified in a cell, and right justified will be a number, (unless you have messed with the formatting) and then you should be able to complete your division.

Hope this helps.

 

[towabc]

It was right justified but nonetheless it worked! Thank you!

 

[Drrellik]

your welcome thank you for the feedback.