Macro to delete all text from cells and leave numbers

[mrmonk21]

I need a macro to delete all text from cells and leave the numbers

Please help

 

[Rick Rothstein]

Hi Rick

I get the same result for Ref = True/False. Maybe you meant:

 ... & Left("!", 1 [B][COLOR=red]+[/COLOR][/B] Ref) & ...

or

 ... & IIf(Ref, "", "!") & ...

Yes, that minus sign should be a plus sign... there I go, tripped up by a last minute edit... again! Thanks for catching that!

For those who don't like to mesh multiple messages together, here is the corrected UDF...

Function TextNum(ByVal Txt As String, Optional Ref As Boolean = False) As String
  Dim X As Long, CellVal As String
  For X = 1 To Len(Txt)
    If Mid(Txt, X, 1) Like "[" & Left("!", 1 + Ref) & "0-9]" Then Mid(Txt, X, 1) = " "
  Next
  TextNum = Replace(Txt, " ", "")
End Function

As for your IIf function suggestion... I try to never use that function unless I can't find a better alternative. IIf is one of the slower VB functions (noticeably slower than its longer If..Then..Else block structure counterpart); and, on top of that, it suffers from the the problem of all arguments being evaluated no matter if the first argument is True or False (although that is not much of a problem here where you are proposing two constants for those arguments). Not using it is pretty much a matter of principle with me any more. Thanks again for spotting my error.

 

[Ron Coderre]

Hi, Rick

Just for the record, I avoid RegExp solutions almost as enthusiastically as I avoid array-formula solutions. They're usually unwieldy and all but impossible for typical users to maintain. However, in occaissional situations, like this thread's issue, RegExp is so much simpler than the LIKE operator approach that I had to yield.

 

[pgc01]

As for your IIf function suggestion... I try to never use that function unless I can't find a better alternative.

Hi Rick

The truth is that I don't think that what your use is a better alternative. That's why I suggested the IIf()

I agree with all you said, the IIf() is

- slower
- always evaluates all the arguments (although this is not a problem here)

but I think a boolean is to be used in a test, not in arithmetic operations.

Your use of the boolean in arithmetic operations

- does not agree with the nature of a boolean (True/False, not numeric)
- is not so easy to read (a beginner will probably not understand it)
- is not portable (True and False may be casted into numbers with different values in different languages)

In conclusion, with your function I would either

- pass Ref as a boolean and then an IIf() or a If Then Else
- pass Ref as an integer and then use the 1 - Ref

This is just my view on the subject, I don't mean to say that your statement is wrong or that it should not be used.

Remark 1:
I use boolean coercion into number in worksheet formulas all the time, but there I'm not worried that it should be portable and the formula structure limits my options.

Remark 2:
It's the first time I've spotted you here. Glad to have you with us!

 

[Ron Coderre]

What about them? In this thread only digits are desired.
Did you want to post a new question in a new thread?

 

[Rick Rothstein]

Hi, Rick

Just for the record, I avoid RegExp solutions almost as enthusiastically as I avoid array-formula solutions. They're usually unwieldy and all but impossible for typical users to maintain. However, in occaissional situations, like this thread's issue, RegExp is so much simpler than the LIKE operator approach that I had to yield.

Ron,

I didn't think my original Like statement line was all that bad

If Mid(CellVal, Z, 1) Like "[!0-9]" Then Mid(CellVal, Z, 1) = " "

however we could have used this statement in its place instead...

If InStr("0123456789", Mid(CellVal, Z, 1)) = 0 Then Mid(CellVal, Z, 1) = " "

Actually, in thinking about it, the InStr statement is probably a touch faster than the Like version. Here is my original macro using Instr instead of Like...

Sub RemoveNonDigits()
  Dim X As Long, Z As Long, LastRow As Long, CellVal As String
  Const StartRow As Long = 1
  Const DataColumn As String = "A"
  Application.ScreenUpdating = False
  LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
  For X = StartRow To LastRow
    CellVal = Cells(X, DataColumn)
    For Z = 1 To Len(CellVal)
      If InStr("0123456789", Mid(CellVal, Z, 1)) = 0 Then Mid(CellVal, Z, 1) = " "
    Next
    With Cells(X, DataColumn)
      .NumberFormat = "@"
      .Value = Replace(CellVal, " ", "")
    End With
  Next
  Application.ScreenUpdating = True
End Sub

And, this InStr version can be used in my UDF as well...

Function TextNum(ByVal Txt As String, Optional Ref As Boolean = False) As String
  Dim X As Long, CellVal As String
  For X = 1 To Len(Txt)
    If InStr("0123456789", Mid(Txt, X, 1)) > 0 = Ref Then Mid(Txt, X, 1) = " "
  Next
  TextNum = Replace(Txt, " ", "")
End Function

There, no Like operator... I guess that is better then, right?

 

[Ron Coderre]

Actually, yes...(if my opinion actually matters to anybody) that code is much more readable.
I still don't like that it has to iterate through every character of every referenced cell. That's a (relative) lot of work compared to the RegExp single-shot per cell capability and the simplicity of the pattern ("\D").

 

[Rick Rothstein]

Actually, yes...(if my opinion actually matters to anybody) that code is much more readable.

I'll bet these are even more readable...

My Macro...

Sub RemoveNonDigits()
  Dim X As Long, Z As Long, LastRow As Long, CellVal As String
  Const StartRow As Long = 1
  Const DataColumn As String = "A"
  Application.ScreenUpdating = False
  LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
  For X = StartRow To LastRow
    CellVal = Cells(X, DataColumn)
    For Z = 1 To Len(CellVal)
      If Not IsNumeric(Mid(CellVal, Z, 1)) Then Mid(CellVal, Z, 1) = " "
    Next
    With Cells(X, DataColumn)
      .NumberFormat = "@"
      .Value = Replace(CellVal, " ", "")
    End With
  Next
  Application.ScreenUpdating = True
End Sub

My UDF...

Function TextNum(ByVal Txt As String, Optional Ref As Boolean = False) As String
  Dim X As Long, CellVal As String
  For X = 1 To Len(Txt)
    If IsNumeric(Mid(Txt, X, 1)) = Ref Then Mid(Txt, X, 1) = " "
  Next
  TextNum = Replace(Txt, " ", "")
End Function

I can't believe I went for the more complicated test to see if a single character is a digit or not. Normally, I stay away from IsNumeric because of its problems when trying to proof number as being just a number, but for single characters, there are no problems using it at all.

As for your concern about the amount of work involved iterating each character... actually, VB is pretty quick about it. IsNumeric, as a Boolean function, should be quite quick, but even the InStr would not have been a problem as it is a very fast VB String function. As for the "string stuffing" that using Mid as a statement allows... that is one of the faster String operations that VB performs (beat out only by Len and Byte array processing I believe), so the loop is really not a bottleneck in my code. The actual slow point would be the Replace function, but on such small String values, its inefficiency would hardly be noticed when compared to any alternatives.

 

[smino21]

Would there be a way to alter that code slightly so that colons ( : ) would be left alone, too? I'm looking to remove all letters and punctuation (except for colons).

Example of what I have:
00:00:03:23 00:00:05:23 The quick brown fox...
00:00:06:23 00:00:12:12 ...jumps over the lazy...
00:00:25:03 00:00:32:11 brown dog!

What I want the macro to do:
00:00:03:23 00:00:05:23
00:00:06:23 00:00:12:12
00:00:25:03 00:00:32:11

At this point, the macro code that I tried, removed the colons, too.
Can anyone help?

 

[Rick Rothstein]

Would there be a way to alter that code slightly so that colons ( : ) would be left alone, too? I'm looking to remove all letters and punctuation (except for colons).

Example of what I have:
00:00:03:23 00:00:05:23 The quick brown fox...
00:00:06:23 00:00:12:12 ...jumps over the lazy...
00:00:25:03 00:00:32:11 brown dog!

What I want the macro to do:
00:00:03:23 00:00:05:23
00:00:06:23 00:00:12:12
00:00:25:03 00:00:32:11

This modification to the macro I posted previously will remove everything except digits and colons...

Sub RemoveNonDigits()
  Dim X As Long, Z As Long, LastRow As Long, CellVal As String
  Const StartRow As Long = 1
  Const DataColumn As String = "A"
  Application.ScreenUpdating = False
  LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
  For X = StartRow To LastRow
    CellVal = Cells(X, DataColumn)
    For Z = 1 To Len(CellVal)
      If Not IsNumeric(Mid(CellVal, Z, 1)) And Mid(CellVal, Z, 1) <> ":" Then Mid(CellVal, Z, 1) = " "
    Next
    With Cells(X, DataColumn)
      .NumberFormat = "@"
      .Value = Replace(CellVal, " ", "")
    End With
  Next
  Application.ScreenUpdating = True
End Sub

 

[smino21]

That's excellent! However, I forgot one thing: the space between the two timecode numbers. Anyway to leave spaces alone, too?

00:00:00:01 00:00:00:34

instead of:
00:00:00:0100:00:00:34


Thank you!