• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Replacing Tab In Delimited Files With Spaces

I'm using an export to text macro, and I was wondering if anyone had a simple way to replace the tabs with spaces in the text file it generates?


I found this,

http://www.codeforexcelandoutlook.com/blog/2010/04/find-and-replace-in-text-files/


But the prob is it will not recognize a single tab space in the "find this text" part.


Any ideas?
 
Hello Dan,


I am afraid that might be too much for me, I really just need an exported text file with the tabs replaced with regular spaces.


Is this one of those easy tasks that get complicated?
 
It will either be making the file internally and exporting as text or saving as a Tab delimitered file.

Can you post the Macro so we can see how it is constructed ?
 
If the find/replace function of a text editor is too much for you, please sign up for an account here:


http://www.aol.com/
 
@Dan


Classic! lol : )

Yes I believe I can manage search and replace once the text file is open. However, I'm trying to run my macro in Excel, have it export the text, and have it perform the search and replacements while my code in Excel proceeds forth with the rest of my code(without stopping to open the text file and manually search and replace the items).


@Hui


Here is the function and sub that search and replaces text in text file.


Any ideas on how I can use this to search and replace tabs?


[pre]<br />
Sub FindAndReplace(filePath As String, findWhat As String, _<br />
replaceWith As String)<br />
' from Excel Help:<br />
' A variable-length string can contain up to approximately 2 billion<br />
' (2^31) characters.<br />
Dim nextFileNum As Long<br />
Dim oldFileContents As String<br />
Dim newFileContents As String<br />
Dim textFileTypes() As String<br />
Dim fileExtension As String</p>
<p> If Len(Dir(filePath)) = 0 Then<br />
Exit Sub<br />
End If</p>
<p> ' only act on "text" files<br />
textFileTypes = QuoteString("txt csv html xml", ",")</p>
<p> fileExtension = LCase$(Right$(filePath, 3))</p>
<p> If UBound(Filter(textFileTypes, fileExtension)) = -1 Then<br />
Exit Sub<br />
End If</p>
<p> ' open file and read contents<br />
nextFileNum = FreeFile</p>
<p> Open filePath For Input As #nextFileNum<br />
oldFileContents = Input$(LOF(nextFileNum), #nextFileNum)<br />
Close #nextFileNum</p>
<p> ' replace old char with new char<br />
newFileContents = Replace(oldFileContents, findWhat, replaceWith)</p>
<p> ' reopen file and write new contents<br />
nextFileNum = FreeFile</p>
<p> Open filePath For Output As #nextFileNum<br />
Print #nextFileNum, newFileContents<br />
Close #nextFileNum</p>
<p>End Sub</p>
<p>Function QuoteString(str As String, delimiter As String) As String()</p>
<p>Dim tempString() As String<br />
Dim newString As String</p>
<p> newString = Replace(str, " ", delimiter)</p>
<p> ' split the string into an array, using delimiter<br />
tempString = Split(newString, delimiter)</p>
<p> QuoteString = tempString</p>
<p>End Function<br />
''''''''''''''''''''''''''''''''''''''''''<br />
''''''''''''''''''''''''''''''''''''''''''<br />
''''''''''''''''''''''''''''''''''''''''''</p>
<p>Sub TestFindAndReplace()</p>
<p> On Error GoTo ErrorHandler<br />
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' find and replace underneath right here<br />
Call FindAndReplace("C:UsersAnonymousDesktopTab Delimited File.txt", "text to find", "text to replace")</p>
<p>ProgramExit:<br />
Exit Sub<br />
ErrorHandler:<br />
MsgBox Err.Number & " - " & Err.Description<br />
Resume ProgramExit<br />
End Sub<br />
''''''''''''''''''''''''''''''''''''''''''</p>
<p>[/pre]
 
It sounds like you want to be able to output to text with a custom delimiter. I don't think any of the built in save as options will let you specify delimiter.


Chip Pearson has some code on outputting to text and specifying delimiter that's I've used before to great success.


http://www.cpearson.com/excel/ImpText.aspx


Jesse
 
@ Jesse


You walked up and shot that one right in the head!


Many thanks the custom delimiter referral worked perfectly.


Solved.
 
Back
Top