Macro that deletes all asterisk signs from cells

0 votes

Say I have a sheet, where the cells are mostly numerical characters only, but some cells unfortunately also contain "*".

I am new to VBA or macros in general and tried this:

Sub RemoveNonNumeric()
Dim c As Range
For Each c In Selection
c.Value = Replace(c.Value, "[^0-9.,-]", "")
Next c
End Sub

My intent is to remove all non-numeric characters except ".","-" and ","

Either the code is faulty or my way of applying it is wrong. Any help?

Jan 22 in Others by Kithuzzz
• 27,740 points
33 views

1 answer to this question.

0 votes

Try this:

Sub RemoveNonNumeric()

    Dim c As Range, regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    With regex
       .Global = True
       .IgnoreCase = True
       .Pattern = "[^0-9.,-]"
    End With
    
    For Each c In Selection
       c.Value = regex.Replace(c.Value, "")
    Next c

End Sub
answered Jan 22 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
0 answers

How to drop all tables from a database with one SQL query?

What if we don't want to type ...READ MORE

May 28, 2022 in Others by Sohail
• 2,960 points
68 views
0 votes
0 answers

How to drop all tables from a database with one SQL query?

drop all tables without typing name. Is ...READ MORE

Jun 7, 2022 in Others by polo
• 1,480 points
64 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 51,240 points
254 views
0 votes
1 answer

In Excel, how to find a average from selected cells

If one has the dynamic array formula ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 51,240 points
145 views
0 votes
1 answer

Can a worksheet ActiveX ComboBox work on a Mac?

ActiveX is an outdated Windows technology that ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 51,240 points
66 views
0 votes
0 answers

Strikethrough in Excel VBA

When I enter the second date in ...READ MORE

Nov 27, 2022 in Others by Kithuzzz
• 27,740 points
68 views
0 votes
1 answer

Excel VBA- Creation of a New datablock with criteria

To insert the dropdown, you can go ...READ MORE

answered Jan 12 in Others by narikkadan
• 51,240 points
33 views
0 votes
1 answer

Delimiters in Excel VBA Regex Patterns, filter something but not others

Change your code to this: Function RemoveTags(ByVal Value ...READ MORE

answered Jan 12 in Others by narikkadan
• 51,240 points
45 views
0 votes
1 answer

Program that works in all version of excel

Knowing that all of your users will ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 51,240 points
93 views
0 votes
1 answer

How can I convert excel to PDF by Libreoffice and keep all format from excel file?

"Times New Roman" typeface does not have ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 51,240 points
440 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP