king daddy
asked on
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.16 8.25.52,,
modified = 159,0548-0587,E101,14.00,BWC,11/17/2010 ,56,192.16 8.25.52,,
I have a text file with several hundred entries that I need to modify.
Thanks a lot
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
modified = 159,0548-0587,E101,14.00,BWC,11/17/2010
I have a text file with several hundred entries that I need to modify.
Thanks a lot
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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
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"
xlTextPrinter, CreateBackup:=False
End Sub
Can you tweak it? Thanks again
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
Thanks again for your help on this
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
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.
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.
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.
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)
Set wbkCopy = Workbooks.Add
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
ASKER
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
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
ASKER
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
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
ASKER
got it. This is what I came up with using a previous macro and some googling.
Thanks all for the help.
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.
ASKER
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.
Open in new window
Book2.xls