Remove dashes between letters and between letters and digits

[webmaster]

Can anyone help me with a script which can remove dashes between 2
letters and between a letter and a digit. But at the same time keeps
the dash beween two digits.

Example:

520-45-3-A-A into 520-45-3AA

I hope I made my example clear!

Mikael

 

[Pete_UK]

If it is always in that format and you want to remove the final two
dashes, then this should do it:

=LEFT(A1,LEN(A1)-4)&MID(A1,LEN(A1)-3,1)&RIGHT(A1,1)

Hope this helps.

Pete

 

[Bernie Deitrick]

Mikael,

Copy the code below into a codemodule of the workbook, then use it like

=FixDashes(A2)

HTH,
Bernie
MS Excel MVP


Function FixDashes(myVal As String) As String
Dim i As Integer
Dim cntr As Integer
FixDashes = myVal
cntr = 2
For i = 2 To Len(myVal) - 1
If Mid(FixDashes, cntr, 1) = "-" Then
If Not (IsNum(Mid(FixDashes, cntr - 1, 1)) _
And IsNum(Mid(FixDashes, cntr + 1, 1))) Then
FixDashes = Left(FixDashes, cntr - 1) & _
Mid(FixDashes, cntr + 1, Len(FixDashes))
Else
cntr = cntr + 1
End If
Else
cntr = cntr + 1
End If
Next i
End Function

Function IsNum(myStr As String) As Boolean
IsNum = False
If Asc(myStr) >= 48 And Asc(myStr) <= 57 Then IsNum = True
End Function

 

[webmaster]

Mikael,

Copy the code below into a codemodule of the workbook, then use it like

=FixDashes(A2)

HTH,
Bernie
MS Excel MVP

Function FixDashes(myVal As String) As String
Dim i As Integer
Dim cntr As Integer
FixDashes = myVal
cntr = 2
For i = 2 To Len(myVal) - 1
   If Mid(FixDashes, cntr, 1) = "-" Then
      If Not (IsNum(Mid(FixDashes, cntr - 1, 1)) _
              And IsNum(Mid(FixDashes, cntr + 1, 1))) Then
         FixDashes = Left(FixDashes, cntr - 1) & _
                     Mid(FixDashes, cntr + 1, Len(FixDashes))
      Else
         cntr = cntr + 1
      End If
   Else
      cntr = cntr + 1
   End If
Next i
End Function

Function IsNum(myStr As String) As Boolean
IsNum = False
If Asc(myStr) >= 48 And Asc(myStr) <= 57 Then IsNum = True
End Function








- Vis tekst i anførselstegn -

thank you very much.....

But it keeps telling me that it expects an End Sub????

 

[Gary''s Student]

Try this UDF:

Function dashless(r As Range) As String
Dim ch() As String
'
' gsnuxx
'

v = r.Value
l = Len(v)
ReDim ch(1 To l)

For i = 1 To l
ch(i) = Mid(v, i, 1)
Next

For i = 2 To l - 1
If ch(i) = "-" Then
If IsNumeric(ch(i - 1)) And IsNumeric(ch(i + 1)) Then
Else
ch(i) = ""
End If
End If
Next

For i = 1 To l
dashless = dashless & ch(i)
Next
End Function

 

[webmaster]

Try this UDF:

Function dashless(r As Range) As String
Dim ch() As String
'
' gsnuxx
'

v = r.Value
l = Len(v)
ReDim ch(1 To l)

For i = 1 To l
    ch(i) = Mid(v, i, 1)
Next

For i = 2 To l - 1
    If ch(i) = "-" Then
        If IsNumeric(ch(i - 1)) And IsNumeric(ch(i + 1)) Then
        Else
            ch(i) = ""
        End If
    End If
Next

For i = 1 To l
    dashless = dashless & ch(i)
Next
End Function

--
Gary''s Student - gsnu2007e







- Vis tekst i anførselstegn -

I does not give me an error but it does not remove the dash when I
test it???

 

[Ron Rosenfeld]

Can anyone help me with a script which can remove dashes between 2
letters and between a letter and a digit. But at the same time keeps
the dash beween two digits.

Example:

520-45-3-A-A into 520-45-3AA

I hope I made my example clear!

Mikael

Here is a User Defined Function that should do what you request (retain only
dashes that are surrounded by numbers, if I understand you correctly).

<alt-F11> opens the VBEditor

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this UDF, enter: =RemoveDashes(cell_ref) into some cell.

==========================================
Option Explicit
Function RemoveDashes(str As String) As String
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
re.Pattern = "(\d+-?\d+)|[0-9A-Z]"
If re.test(str) = True Then
Set mc = re.Execute(str)
For Each m In mc
RemoveDashes = RemoveDashes & m
Next m
End If
End Function
==================================
--ron

 

[Bernie Deitrick]

You shouldn't have any Sub declarations in the codemodule, since both pieces of code that I posted
are User-Defined-Functions.

HTH,
Bernie
MS Excel MVP

Mikael,

Copy the code below into a codemodule of the workbook, then use it like

=FixDashes(A2)

HTH,
Bernie
MS Excel MVP

Function FixDashes(myVal As String) As String
Dim i As Integer
Dim cntr As Integer
FixDashes = myVal
cntr = 2
For i = 2 To Len(myVal) - 1
If Mid(FixDashes, cntr, 1) = "-" Then
If Not (IsNum(Mid(FixDashes, cntr - 1, 1)) _
And IsNum(Mid(FixDashes, cntr + 1, 1))) Then
FixDashes = Left(FixDashes, cntr - 1) & _
Mid(FixDashes, cntr + 1, Len(FixDashes))
Else
cntr = cntr + 1
End If
Else
cntr = cntr + 1
End If
Next i
End Function

Function IsNum(myStr As String) As Boolean
IsNum = False
If Asc(myStr) >= 48 And Asc(myStr) <= 57 Then IsNum = True
End Function








- Vis tekst i anførselstegn -

thank you very much.....

But it keeps telling me that it expects an End Sub????