Can excel perform this function?

[googlealchemist]

I have large text files that I want to add various characters every X number of words to separate them. Can excel/macros/anything else perform a function like this? Inserting whatever character/s or string of text that I want every X (defined by me) number of characters/words in the whole file?

Thank you.

 

[Joe4]

Welcome to the Board!

Can Excel do this? Possibly. I think we would need to see an actual example of exactly what you are trying to do. I am thinking it could require a Macro, but not necessarily depending on what your text looks like and exactly what you are trying to insert and where.

There are some tools you can use to post screen images. They are mentioned in this thread: http://www.mrexcel.com/forum/showthread.php?t=508133

 

[googlealchemist]

Hey joel thanks a lot for the reply.

It's basically a file filled with many different articles that at the present run together. I want to add a pipe symbol every so often that will allow me to sort it better in other programs that I use.

So if something could allow me to run thru a file like this and pop in a | every 500 or 1000 or whatever words that I specify that would be great.

 

[Joe4]

But a lot will actually depend on how it looks in Excel.
Will it all come in on one row, or will it be on multiple rows?

Creating a macro to insert something every "X" number of characters (per row) shouldn't be too bad, but every "X" number of words could prove to be a bit tricky.

 

[googlealchemist]

But a lot will actually depend on how it looks in Excel.
Will it all come in on one row, or will it be on multiple rows?

Creating a macro to insert something every "X" number of characters (per row) shouldn't be too bad, but every "X" number of words could prove to be a bit tricky.

Hey joe, sorry I misread your name before. Every x number of characters would be fine, it certainly doesn't have to be words. I suppose I could put it all on one row or across all rows depending on what would be more effecient.

I am brand spankin' new to excel so...

 

[Joe4]

That really depends on whether or not it has carriage returns in the text file.

Try opening/importing the text file in Excel and let me know if it is putting everything in one cell/row, or multiple rows.

 

[googlealchemist]

That really depends on whether or not it has carriage returns in the text file.

Try opening/importing the text file in Excel and let me know if it is putting everything in one cell/row, or multiple rows.

I have another tool that allows me to put in or take out carriage returns...I think.

When I am in the spreadsheet section and try to open the text file in question it opens it up in a text doc, it does not upload it into the spreadsheet I have open. I can copy paste it there though which is what I have been doing.

Thats with a plain notepad text file, If I upload an excel file that I saved it spreads it out across multiple columns/rows/cells

 

[Joe4]

When I am in the spreadsheet section and try to open the text file in question it opens it up in a text doc

How exactly are you opening it?
If you open it in Excel, it may still have the "txt" extension, but the data should appear on an Excel spreadsheet. If it appears as a text document (if it opens in NotePad or some other text editor), then it is not opening in Excel.

 

[Joe4]

OK, assuming that once you open the file in Excel, all the data appears in column A on multiple rows, here is a macro that will insert your "delimiter" every "X" number of spaces on each row. You can change/enter your delimiter and spacing right in the code:

Option Explicit
 
Sub InsertDelim()
 
    Dim myDelim As String
    Dim mySpacing As Long
    Dim myLastRow As Long
    Dim myLen As Long
    Dim myOrigString As String
    Dim myNewString As String
    Dim myNumDelim As Long
    Dim i As Long
    Dim j As Long
    
    Application.ScreenUpdating = False
        
'   Choose delimiter
    myDelim = "|"
    
'   Choose number of spaces between delimiters
    mySpacing = 50
    
'   Find last row
    myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 1 To myLastRow
        myOrigString = Cells(i, "A")
        myLen = Len(myOrigString)
        myNumDelim = Int(myLen / mySpacing)
        If myNumDelim > 0 Then
            myNewString = ""
            For j = 1 To myNumDelim
                myNewString = myNewString & Mid(myOrigString, (j - 1) * mySpacing + 1, mySpacing) & myDelim
            Next j
            Cells(i, "A") = Left(myNewString, Len(myNewString) - 1)
        Else
            Cells(i, "A") = myOrigString
        End If
    Next i
    
    Application.ScreenUpdating = True
    
End Sub