• Status: Solved
• Priority: Medium
• Security: Public
• Views: 801

# Excel macro to add hyphen

Greetings,

can someone post a macro that will add a hyphen between the 8th and 9th digits in the line below

original = 159,05480587,E101,14.00,BWC,11/17/2010,56,192.168.25.52,,

modified = 159,0548-0587,E101,14.00,BWC,11/17/2010,56,192.168.25.52,,

I have a text file with several hundred entries that I need to modify.

Thanks a lot
0
• 6
• 4
• 2
• +1
3 Solutions

Commented:
Does it have to be a macro? If not, then you could just copy this formula down (assuming your data are in column A):

=LEFT(A1, FIND(",", A1)+4)& "-" & RIGHT(A1, LEN(A1) - FIND(",", A1)-4)

0

Commented:
If it is a string then this would change it
``````original = 159,05480587,E101,14.00,BWC,11/17/2010,56,192.168.25.52,,

original = left\$(original,8) & "-" & mid\$(original,9)
``````
0

VBA DeveloperCommented:
For a macro, try this:
``````Option Explicit

Sub InsertHypen()

Dim i As Long
Dim lastRow As Long
Dim tempValue As String

lastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastRow
tempValue = Cells(i, 1).Value
If tempValue <> "" Then Cells(i, 2).Value = Left(tempValue, 8) & "-" & Right(tempValue, Len(tempValue) - 8)
Next i

End Sub
``````
Book2.xls
0

Commented:
It's worth noting that my formula works assuming you just want the second item in the comma-delimited list. If there is another comma, then the formula will fail.

At the very least, it won't matter if the first entry is one digit or eight digits. It only looks for that first comma.
0

Author Commented:
jkunrein: it does have to be a macro. Thanks

borgunit: thanks

broomee9: this adds the hyphen - thanks. However, it creates a new column with the hyphen. I would like it to create a new text doc with the hyphen and leave the original file alone. Also, this seems to skip the first entry. This is what I have:

Sub Copy()
'
' Copy Macro
'
' Keyboard Shortcut: Ctrl+q
'
Workbooks.OpenText Filename:="C:\copy.txt"

Dim i As Long
Dim lastRow As Long
Dim tempValue As String

lastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastRow
tempValue = Cells(i, 1).Value
If tempValue <> "" Then Cells(i, 2).Value = Left(tempValue, 8) & "-" & Right(tempValue, Len(tempValue) - 8)
Next i

ActiveWorkbook.SaveAs Filename:="C:\copyMAC.txt", FileFormat:= _
xlTextPrinter, CreateBackup:=False

End Sub

Can you tweak it? Thanks again
0

VBA DeveloperCommented:
>>Also, this seems to skip the first entry.
That's because it assumes headers with this line, just need to change the 2 to a 1:

For i = 2 To lastRow

``````Sub Copy()
'
' Copy Macro
'
' Keyboard Shortcut: Ctrl+q
'

Dim wbkOrig As Workbook
Dim wkShtOrig As Worksheet
Dim wbkCopy As Workbook
Dim i As Long
Dim lastRow As Long
Dim tempValue As String

Set wbkOrig = ThisWorkbook
Set wkShtOrig = wbkOrig.Worksheets(1)
Set wbkCopy = Workbooks.Open("C:\copy.txt")

lastRow = wkShtOrig.Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To lastRow
tempValue = wkShtOrig.Cells(i, 1).Value
If tempValue <> "" Then wbkCopy.Worksheets(1).Cells(i, 1).Value = Left(tempValue, 8) & "-" & Right(tempValue, Len(tempValue) - 8)
Next i

wbkCopy.SaveAs Filename:="C:\copyMAC.txt", FileFormat:= _
xlTextPrinter, CreateBackup:=False

Set wkShtOrig = Nothing
Set wbkOrig = Nothing
Set wbkCopy = Nothing

End Sub
``````
Book2.xls
0

Author Commented:
thanks - but it didn't work. It didn't produce the extra column, but it didn't add the hyphen either. Here is what I have now:

``````Dim wbkOrig As Workbook
Dim wkShtOrig As Worksheet
Dim wbkCopy As Workbook
Dim i As Long
Dim lastRow As Long
Dim tempValue As String

Set wbkOrig = ThisWorkbook
Set wkShtOrig = wbkOrig.Worksheets(1)
Set wbkCopy = Workbooks.Open("C:\copy.txt")

lastRow = wkShtOrig.Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To lastRow
tempValue = wkShtOrig.Cells(i, 1).Value
If tempValue <> "" Then wbkCopy.Worksheets(1).Cells(i, 1).Value = Left(tempValue, 8) & "-" & Right(tempValue, Len(tempValue) - 8)
Next i

wbkCopy.SaveAs Filename:="C:\copyMAC.txt", FileFormat:= _
xlTextPrinter, CreateBackup:=False

Set wkShtOrig = Nothing
Set wbkOrig = Nothing
Set wbkCopy = Nothing
``````

Thanks again for your help on this
0

VBA DeveloperCommented:
This post works for me: 34233604

Add a blank text file to the C:\ drive and rename it to copy.txt.

Then run the macro and the new file called copyMAC.txt will be created with the data and the hypen in it.
0

VBA DeveloperCommented:
If you can't make it work with that, then this will work without the copy.txt file existing.

By the way, the copy.txt file is only in there because of the code you posted.  It's not actually needed.
``````Option Explicit

Sub Copy()
'
' Copy Macro
'
' Keyboard Shortcut: Ctrl+q
'

Dim wbkOrig As Workbook
Dim wkShtOrig As Worksheet
Dim wbkCopy As Workbook
Dim i As Long
Dim lastRow As Long
Dim tempValue As String

Set wbkOrig = ThisWorkbook
Set wkShtOrig = wbkOrig.Worksheets(1)

lastRow = wkShtOrig.Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To lastRow
tempValue = wkShtOrig.Cells(i, 1).Value
If tempValue <> "" Then wbkCopy.Worksheets(1).Cells(i, 1).Value = Left(tempValue, 8) & "-" & Right(tempValue, Len(tempValue) - 8)
Next i

wbkCopy.SaveAs Filename:="C:\copyMAC.txt", FileFormat:= _
xlTextPrinter, CreateBackup:=False

Set wkShtOrig = Nothing
Set wbkOrig = Nothing
Set wbkCopy = Nothing

End Sub
``````
Book2-v2.xls
0

Author Commented:
OK. I opened the copy.txt file with Excel, added the macro code from your 34233604 post, saved it as a macro-enabled excel workbook, clicked crtl+q and it worked.

However, I need to be able to open a blank workbook, except for the macro, have it pull the data from the copy.txt file and output the changes to the copyMAC.txt file. The office manager will not open the copy.txt in Excel, or notepad for that matter, but rather open the excel macro, run it (ctrl+q), then import the copyMAC.txt file into the reporting system. Any ideas on how to accomplish that? I apologize if my post was not explained properly.

Thanks
0

Author Commented:
posted above before seeing your last post, 34233810.

The copy.txt is needed as it holds the original file that is output by the copy system. the file format does not work with the new reporting software so we need a macro to alter the original file by adding the hyphen so the reports are formatted correctly.

Thanks
0

Author Commented:
got it. This is what I came up with using a previous macro and some googling.

``````Workbooks.OpenText Filename:="C:\copy.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1)), TrailingMinusNumbers:=True
Columns("B:B").Select
Selection.NumberFormat = "0000-0000"
Columns("F:F").ColumnWidth = 10.57
Columns("D:D").Select
Selection.NumberFormat = "0.00"
ActiveWorkbook.SaveAs Filename:="C:\copyMAC.txt", FileFormat:= _
xlCSV, CreateBackup:=False
``````

Thanks all for the help.
0

Author Commented:
while the macro posted and accepted did what I asked in that it put the hyphen in where I wanted, it didn't do it exactly how I wanted which is why I posted what did work for my situation. That said, thanks for helping out to all who posted.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.