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.

 

[RSXchin]

let me know if this works for you

Sub Macro1()
    With Range("A2:K2")
        .Replace What:="%", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        .Replace What:="|", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        .Formula = Trim(.Text)
    End With
End Sub

 

[B.Kellough]

it clears all of the data out of each cell, kind of like the "Clear Contents" command.

 

[RSXchin]

works fine for me. is there anything else in the cells that is messing it up?

 

[B.Kellough]

Nope, it just clears all of the cells out. I am going to try it in another spreadsheet.

 

[Peter_SSs]

Wipes everything for me too. Why not just do the replaces (including one for spaces as mentioned in the OP)?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Macro1()<br>    <SPAN style="color:#00007F">With</SPAN> Range("A2:K2")<br>        .Replace What:="%", Replacement:="", LookAt:=xlPart, _<br>            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=<SPAN style="color:#00007F">False</SPAN>, _<br>            ReplaceFormat:=False<br>        .Replace What:="|", Replacement:="", LookAt:=xlPart, _<br>            SearchOrder:=xlByRows, MatchCase:=<SPAN style="color:#00007F">False</SPAN>, SearchFormat:=False, _<br>            ReplaceFormat:=<SPAN style="color:#00007F">False</SPAN><br>        .Replace What:=" ", Replacement:="", LookAt:=xlPart, _<br>            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _<br>            ReplaceFormat:=False<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>

 

[B.Kellough]

Peter, that works perfectly. thanks

One question; I also have a foemula in M2 that is suppose to get the average of the numbers in A2:F2 and that has stopped working, any suggestions?

 

[Peter_SSs]

Peter, that works perfectly. thanks

One question; I also have a foemula in M2 that is suppose to get the average of the numbers in A2:F2 and that has stopped working, any suggestions?

What exactly is the formula?

 

[B.Kellough]

=average(A2:F2)

 

[Peter_SSs]

Looks OK. You say it has "stopped working". What do you mean? What is showing in M2? What is in A2:F2?