On 5 Mar., 15:06, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
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

wrote in message

...



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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

thank you very much.....

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

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


wrote in message
...
On 5 Mar., 15:06, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
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

wrote in message

...



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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

thank you very much.....

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