In support of this main forum Thread post:
https://excelfox.com/forum/showthrea...ll=1#post16695
content in one line input reduced sample.txt https://app.box.com/s/grrxh1rl372pzp2exn6em00ovhj4qhih
content in one line output reduced sample.txt https://app.box.com/s/mpyvgf4kj9q04szjtj0255cns24lbxos
What’s those two text files got in them?:
Using the Function , Sub WtchaGot_Unic_NotMuchIfYaChoppedItOff(ByVal strIn As String, Optional ByVal FlNme As String) '
, from here: https://excelfox.com/forum/showthrea...ll=1#post15524
https://pastebin.com/eutzzxHv
, and using this Calling macro
Code:
' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16696&viewfull=1#post16696
Sub SantaComing()
' Rem 1 Get the text file as a long single string
Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "content in one line input reduced sample.txt" '
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input...
' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length
'Get #FileNum, , TotalFile
' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782
Let TotalFile = Input(LOF(FileNum), FileNum)
Close #FileNum
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile)
End Sub
Here is the results, for example from the immediate window :
Code:
Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems " & vbCr & vbLf & "in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short " & vbCr & vbLf & "stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & vbCr & vbLf & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story " & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that " & vbCr & vbLf & "the story has somehow wormed"
Here are the corresponding results if I do the same to look at the after file ( content in one line output reduced sample.txt https://app.box.com/s/mpyvgf4kj9q04szjtj0255cns24lbxos )
Code:
Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that the story has somehow wormed"
So, what do we conclude?:
_ 1) there are no tab characters
_ 2) It seems that the line separator is the commonly used 2 character pair of a carriage return and a line feed, ( In VBA coding, vbCr & vbLf )
_ 3) It seems like the character string we wish to remove, ( the extra new line forming characters ) are three character:
A space and a carriage return and a line feed. ( In VBA coding _ " " & vbCr & vbLf _ )
( _ 4) I expect the OP, ( susan santa 12345 et al. ) , probably would not want to remove all that, but rather replace it with a single space , so I will do that!)
This initial macro seems to do the required:
Code:
Sub ReplaceInTextFileThreeCharacters__Space_vbCr_vbLf__WithA__Space__() ' https://excelfox.com/forum/showthread.php/2817-Make-all-text-file-content-in-one-line-if-a-space-found
' Rem 1 Get the text file as a long single string
Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "content in one line input reduced sample.txt" '
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input...
' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length
'Get #FileNum, , TotalFile
' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782
Let TotalFile = Input(LOF(FileNum), FileNum)
Close #FileNum
Rem 2 remove " " & vbCr & vbLf
' Let TotalFile = Replace(TotalFile, " " & vbCr & vbLf, "", 1, -1, vbBinaryCompare)
Let TotalFile = Replace(TotalFile, " " & vbCr & vbLf, " ", 1, -1, vbBinaryCompare) ' I expect the OP, ( susan santa 12345 et al. ) , probably would not want to remove all that, but rather replace it with a single space , so I do that!
Rem 3 Make new file
Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "new text file.txt"
Dim FileNum2 As Long: Let FileNum2 = FreeFile(0) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Open PathAndFileName For Output As #FileNum2
Print #FileNum2, TotalFile ' write out entire text file
Close #FileNum2
End Sub
Here is the corresponding output from my function for the new text file made
Code:
Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that the story has somehow wormed" & vbCr & vbLf
That looks very similar to the output requested. Here is the new text file made:
new text file.txt https://app.box.com/s/w2zydwa20lr3jyxuk8z7ddjgpl0xrcvn
SantaClawsIsComing.xls https://app.box.com/s/7ken6nf050xd4xqwhxu3av1yebdb74dr
Reply With Quote
Originally Posted by sandy666
Bookmarks