Adding Hyphen in Between of Strings Using Macros

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
195
Office Version
  1. 2013
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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,397
Office Version
  1. 365
Platform
  1. Windows
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

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,397
Office Version
  1. 365
Platform
  1. Windows
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:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,397
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

madhuchelliah

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,397
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,139
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
 

Forum statistics

Threads
1,136,258
Messages
5,674,669
Members
419,520
Latest member
Jennifer4Dillon

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
Top