Adding Hyphen in Between of Strings Using Macros

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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?
 
Upvote 0
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.
 
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0
On re-reading, I think my _v1 should be this.

Rich (BB code):
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
 
Last edited:
Upvote 0
On re-reading, I think my _v1 should be this.

Rich (BB code):
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.
 
Upvote 0
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
Rich (BB code):
a = Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(1)).Value
 
Upvote 0
Here is a non-RegExp macro that should also work...
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top