Adding Hyphen in Between of Strings Using Macros

[madhuchelliah]

Hello Mates, i want to insert a hyphen in between of strings in the A column. My string format is AA111AA111 BB222BB222,....... A space is separating the strings. Number of strings are dynamic. Hyphen should be inserted exactly middle of the string that is after 5th character. So the result should be like this AA111-AA111 BB222-BB222 displayed in B column. Thank you.

 

[Peter_SSs]

One example is not a lot to go on.

Are all the values in column A 2 sets of 10 characters with a space between? If not, could we have a few more samples showing the variety of data and the expected results?

 

[madhuchelliah]

One example is not a lot to go on.

Are all the values in column A 2 sets of 10 characters with a space between? If not, could we have a few more samples showing the variety of data and the expected results?

Thanks Pete for the prompt reply. Values in the cells are not limited to 2 sets it is a variable. It could be from minimum of 1 to maximum of 1000 sets. Data format is always 10 characters. 1st,2nd,6th and 7th are could be alphabets or alpha numeric. 3rd, 4th, 5th, 8th, 9th and 10th characters are always numeric. Values are always separated by a single space. Hope you get the input. Thanks again.

 

[Peter_SSs]

Edit: I think that I have misinterpreted the data, I'll post again soon in case this was incorrect (at least the first option)


Here are a couple of macros for your consideration.
Suggest you test on a copy of a smallish set of data to start with.
I've assumed first value is in A2.

Sub Insert_Dashes_v1()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(\w{5})(\w{5} \w{5})(\w{5})"
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    a(i, 1) = RX.Replace(a(i, 1), "$1-$2-$3")
  Next i
  Range("B2").Resize(UBound(a)).Value = a
End Sub

Sub Insert_Dashes_v2()
  Dim a As Variant
  Dim i As Long, j As Long
  Dim s As String
  
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    s = a(i, 1)
    For j = Len(s) - 5 To 5 Step -11
      s = Left(s, j) & "-" & Mid(s, j + 1)
    Next j
    a(i, 1) = s
  Next i
  Range("B2").Resize(UBound(a)).Value = a
End Sub

 

[Peter_SSs]

On re-reading, I think my _v1 should be this.

Sub Insert_Dashes_v1()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(\w{5})(\w{5})"
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    a(i, 1) = RX.Replace(a(i, 1), "$1-$2")
  Next i
  Range("B2").Resize(UBound(a)).Value = a
End Sub

 

[madhuchelliah]

On re-reading, I think my _v1 should be this.

Sub Insert_Dashes_v1()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(\w{5})(\w{5})"
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    a(i, 1) = RX.Replace(a(i, 1), "$1-$2")
  Next i
  Range("B2").Resize(UBound(a)).Value = a
End Sub

Hello Pete, both versions are working great. But one issue, that is if i have data in A2 cell alone it is throwing a error. Apart from this code is working fine. Thank you.

 

[Peter_SSs]

Hello Pete, both versions are working great. But one issue, that is if i have data in A2 cell alone it is throwing a error. Apart from this code is working fine. Thank you.

You're welcome.

Yes, a single cell of data would cause an error. In either version make this change

a = Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(1)).Value

 

[Rick Rothstein]

Here is a non-RegExp macro that should also work...

Sub Insert_Dashes_v2()
  Dim R As Long, C As Long, Data As Variant, Codes As Variant
  Data = Range("A2", Cells(Rows.Count, "A").End(xlUp).Offset(1))
  For R = 1 To UBound(Data)
    Codes = Split(Data(R, 1))
    For C = 0 To UBound(Codes)
      Codes(C) = Format(Codes(C), "@@@@@-@@@@@")
    Next
    Data(R, 1) = Join(Codes)
  Next
  Range("B2").Resize(UBound(Data)) = Data
End Sub

 

[Peter_SSs]

Here is a non-RegExp macro that should also work...

That is an alternative non-regExp macro, as one was already given in post 4.

 

[madhuchelliah]

Thank you both of you