Deleting dashes from text & numbers

  • Thread starter Thread starter chainsaw
  • Start date Start date
C

chainsaw

I am needing to delete dashes (minus) from a cell of text and numbers.
Some programs call this butting where everything is eliminated excep
A-Z & 0-9.

Example

AAG-70-1234-987

Needs to read: AAG701234987

The dashes are not always in the same spot.

AAG-701234

AAG-70-7-12-45

Thanks in advance
 
If you want to do it in place, using a macro:

Public Sub AlphaNumerics()
Dim i As Long
Dim rCell As Range
Dim sOut As String

For Each rCell In Selection
With rCell
For i = 1 To Len(.Text)
If Mid(.Text, i, 1) Like "[A-Za-z0-9]" Then
sOut = sOut & Mid(.Text, i, 1)
End If
Next i
End With
rCell.Value = sOut
sOut = ""
Next rCell
End Sub
 
JE McGimpsey said:
If you want to do it in place, using a macro:

Public Sub AlphaNumerics() ....
For Each rCell In Selection
With rCell
For i = 1 To Len(.Text)
If Mid(.Text, i, 1) Like "[A-Za-z0-9]" Then
sOut = sOut & Mid(.Text, i, 1)
Click to expand...
....

Why not replace all this with

For Each rCell in Selection
With rCell
.Value = Application.Worksheetfunction.Substitute(.Text, "-", "")
End With
Next rCell

? I realize that this macro removes more than just dashes, but it's a fair
bet calling the VBScript RegExp object would be about as fast as building
strings a character at a time while allowing considerably greater
flexibility.
 
Harlan Grove said:
Why not replace all this with

For Each rCell in Selection
With rCell
.Value = Application.Worksheetfunction.Substitute(.Text, "-", "")
End With
Next rCell
Click to expand...

Because the OP had mentioned removing all non-alphanumeric characters,
and I'd already posted a worksheet function solution for dashes using
Substitute().
? I realize that this macro removes more than just dashes, but it's a fair
bet calling the VBScript RegExp object would be about as fast as building
strings a character at a time while allowing considerably greater
flexibility.
Click to expand...

But VBScript is not a cross-platform solution, and I greatly prefer
routines that run unmodified on WinXL97+/MacXL98+. Granted, with Mac OSX
I could call sed or a Perl RegExp routine just as easily, but that would
require conditional compilation as well.
 
JE McGimpsey said:
Because the OP had mentioned removing all non-alphanumeric characters,
and I'd already posted a worksheet function solution for dashes using
Substitute().
Click to expand...

Ambiguous, but I'll grant the point.
But VBScript is not a cross-platform solution, and I greatly prefer
routines that run unmodified on WinXL97+/MacXL98+. Granted, with Mac OSX
I could call sed or a Perl RegExp routine just as easily, but that would
require conditional compilation as well.
Click to expand...

OK. Different pickiness. There's no reason to process cells containing no
non-alphanumeric characters, and a few Substitute calls may be more
efficient than building up strings character by character, but that'd be a
function of the actual data.

Anyway, an alternative.


Sub foo()
Dim c As Range, i As Long
Dim t As String, ch As String * 1

If Not TypeOf Selection Is Range Then Exit Sub

For Each c In Selection
t = c.Text

If t Like "*[!0-9A-Za-z]*" Then
i = Len(t)

Do While i > 0
ch = Mid(t, i, 1)

If ch Like "[!0-9A-Za-z]" Then
t = Application.WorksheetFunction.Substitute(t, ch, "")
i = Len(t)

Else
i = i - 1

End If

Loop

c.Value = t

End If

Next c

End Sub
 
Harlan Grove said:
OK. Different pickiness. There's no reason to process cells containing no
non-alphanumeric characters, and a few Substitute calls may be more
efficient than building up strings character by character, but that'd be a
function of the actual data.

Anyway, an alternative.
Click to expand...

Agreed, and for an even more simplifying assumption, there's no reason
to expect that the user wants to replace formulae with text, so your
alternative could be even more efficient by replacing:

For Each c In Selection
...
Next c

with

On Error Resume Next
For Each c In Selection.SpecialCells( _
Type:=xlCellTypeConstants, _
Value:=xlTextValues)
...
Next c
On Error GoTo 0

which eliminates functions, blanks and numbers.
 
Back
Top