Insert dashes within character string, within an array.

[pgiering]

ISO VBA command to insert a dash (hyphen) as the 5th character from the left, and 6th character from the right, in a string of varying length, within an array.

For example...
Column Z contains the following list of values:
MSEAVIMA0013S
MSEAVIRM0016S
MOLPVIRM0017S
MOLS130012S
MSEAVIRM0011S
MOL21300040S
MSEAVIMA0008S

I would like to run a macro which converted them to:
MSEA-VIMA-0013S
MSEA-VIRM-0016S
MOLP-VIRM-0017S
MOLS-13-0012S
MSEA-VIRM-0011S
MOL2-130-0040S
MSEA-VIMA-0008S

The rows may vary (e.g. - it might be 2-8, or it might be 3-49, etc.), so I would plan to select the range before running the macro.

Thank you!

Paul

 

[Peter_SSs]

If you are interested, here is another macro that you could try after selecting the range you want processed.

Sub Insert_Dashes()
  Dim c As Range
  
  With CreateObject("VBScript.RegExp")
    .Pattern = "(^.{4})(.+)(.{5}$)"
    For Each c In Selection
      If .test(c.Value) Then c.Value = .Replace(c.Value, "$1-$2-$3")
    Next c
  End With
End Sub

 

[lrobbo314]

@Peter_SSs I didn't even think to use regular expressions. Slick solution!