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

On Mar 5, 1:45 pm, webmas...@msphoto.dk 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

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

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