Macro to pull data from A2:K2

[B.Kellough]

I'm needing a macro that will pull data from A2:K2 and remove everything from the data except for the numbers only. for example A2 has "|16.51%" in the cell, the macro would remove "|" and "%" and leave the 16.51 and remove any spaces before the 16.51.

Thanks in advance for any help with this.

 

[B.Kellough]

I'm getting "DIV/0!" where the average should be.

 

[Peter_SSs]

You didn't answer my question about what is in A2:F2

If you are getting that after running the macro, it would indicate that the values in A2:F2 are not numbers.

Do they look like numbers?

In a blank cell put
=ISNUMBER(A2)
and drag that right to fill 6 horizontal cells. The results I presume are FALSE

In another blank cell
=LEN(A2)
and drag right. Do those results seem unusual?

 

[B.Kellough]

yes it all equals false, and yes the results are all "5". What did I mess up?

 

[Peter_SSs]

My guess is that the 'space' that we removed was not actually a space so it is still in the cell.

Put this in a vacant cell. I put this in K11, if you use a different cell change the K11s in the formula to the cell where you put the formula. Make sure you keep the $ signs where they are.

=CODE(MID($A$2,ROWS(K$11:K11),1))

Now drag the formula down to fill 5 cells.

Tell us what those 5 values are AND what you are seeing in cell A2.

 

[B.Kellough]

A2 has "8.34" in it. After pasting the formula in cell "P11" and dragging down 5 here is what I get 160, 56, 46, 51, 52.

I changed the K11 in the formula to P11.

 

[Peter_SSs]

OK, in the code edit the final 'Replace' line like this

.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

 

[B.Kellough]

You are a genius; perfect!
I can't thank you enough.

 

[Peter_SSs]

Thanks, glad we got there in the end.