How do I translate them to standard format 1234567-01 using vba?

How do I format these

123-4267-01
12-34-56-701
1234567-01
1-2345-6701
12345670-1
123456701
1234567_01
and more fromats, note it may have - or may be _ in between

Now I have to convert the above types to these standard

 1234567开发者_Go百科-01

Probably I think i should go for find but what i have to use xlatwhole or xlat? Or are there any better solutions than find to achieve these because I have 4000 rows so I need some faster performance better than looping.

I may have other than numeric numbers.

With VBA, just replace the dodgy _ & - characters & reformat;

Public Function fixup(sValue As String) As String
   fixup = Replace$(sValue, "_", "")
   fixup = Replace$(fixup, "-", "")
   fixup = Left$(fixup, Len(fixup) - 2) & "-" & Right$(fixup, 2)
End Function

Or to strip any non-numeric;

Public Function fixup(sValue As String) As String
    Dim i As Long
    For i = 1 To Len(sValue)
        If Not IsNumeric(Mid$(sValue, i, 1)) Then Mid$(sValue, i, 1) = "!"
    Next
    fixup = Replace$(sValue, "!", "")
    fixup = Left$(fixup, Len(fixup) - 2) & "-" & Right$(fixup, 2)
End Function

If you are looking for fast performance, you could meet your requirement using an Excel worksheet function rather than VBA.

=LEFT(SUBSTITUTE(SUBSTITUTE(A1,"_",""),"-",""),LEN(SUBSTITUTE(SUBSTITUTE(A1,"_",""),"-",""))-2)&"-"&RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"_",""),"-",""),2)

Assuming your target range starts in Cell A1, type this into the first row and copy down the whole range. Excel on my pc did c. 45,000 rows in < 1 second.

(NB: I am only suggesting this approach because you have tagged your question as excel-vba)