Solved

Excel macro to add hyphen

Posted on 2010-11-29
13
743 Views
Last Modified: 2012-05-10
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
Comment
Question by:rpliner
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 9

Assisted Solution

by:jkunrein
jkunrein earned 75 total points
ID: 34233272
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
 
LVL 10

Assisted Solution

by:borgunit
borgunit earned 75 total points
ID: 34233295
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)

Open in new window

0
 
LVL 24

Expert Comment

by:broomee9
ID: 34233312
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

Open in new window

Book2.xls
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:jkunrein
ID: 34233313
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
 
LVL 7

Author Comment

by:rpliner
ID: 34233499
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
 
LVL 24

Accepted Solution

by:
broomee9 earned 350 total points
ID: 34233604
>>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

Try this instead.
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

Open in new window

Book2.xls
0
 
LVL 7

Author Comment

by:rpliner
ID: 34233689
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

Open in new window


Thanks again for your help on this
0
 
LVL 24

Expert Comment

by:broomee9
ID: 34233764
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
 
LVL 24

Expert Comment

by:broomee9
ID: 34233810
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)
    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

Open in new window

Book2-v2.xls
0
 
LVL 7

Author Comment

by:rpliner
ID: 34234281
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
 
LVL 7

Author Comment

by:rpliner
ID: 34234329
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
 
LVL 7

Author Comment

by:rpliner
ID: 34242395
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

Open in new window


Thanks all for the help.
0
 
LVL 7

Author Closing Comment

by:rpliner
ID: 34242451
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Power Query Grouping By 2 13
Calculate number of months in Excel 13 25
Excel formula to append date to end of url 6 28
Tricky shapes formula part 2 4 19
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question