Remove dashes between letters and between letters and digits
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????
|