jumbled column contents excel to .txt

%3CLINGO-SUB%20id%3D%22lingo-sub-1887473%22%20slang%3D%22en-US%22%3Ejumbled%20column%20contents%20excel%20to%20.txt%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1887473%22%20slang%3D%22en-US%22%3E%3CP%3EI%20use%20a%20script%20to%20migrate%20data%20into%20an%20.xml%20environment%3B%20the%20first%20step%20is%20to%20save%20an%20Excel%20sheet%20containing%20that%20dataset%20as%20tab-delimited%20text%20(this%20is%20non-numeric%20data).%20In%20long%20blocks%20of%20text-based%20data%20within%20a%20cell%2C%20I%20often%20insert%20line%20breaks%20(Alt%20%2B%20Return).%20However%2C%20in%20the%20resulting%20.txt%20file%2C%20any%20data%20after%20a%20line%20break%20is%20bumped%20into%20the%201st%20column.%20I%20don't%20remember%20having%20this%20problem%20with%20earlier%20versions%20of%20Excel.%20Is%20Excel%20interpreting%20these%20line%20breaks%20as%20tabs%3F%20Is%20there%20a%20way%20to%20keep%20the%20line%20breaks%20without%20this%20column%20reassignment%3F%20Failing%20that%2C%20is%20there%20a%20quick%20way%20to%20delete%20all%20the%20line%20breaks%20in%20a%20column%3F%20Thanks%20for%20help%20with%20this%20mystery.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1887473%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1888000%22%20slang%3D%22de-DE%22%3ESubject%3A%20jumbled%20column%20contents%20excel%20to%20.txt%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1888000%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F869107%22%20target%3D%22_blank%22%3E%40MWheel65%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EHere%20is%20a%20VBA%20code%20-%20untested%20...%20but%20it%20should%20work%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20example%20code%20()%0ADim%20Regex%20As%20Object%0ADim%20meAr%20()%0ADim%20nCount%20%26amp%3B%2C%20MaxRow%20%26amp%3B%2C%20lngTeil%20%26amp%3B%2C%20lngStep%20%26amp%3B%0ADim%20rng%20range%20As%20Range%0ASet%20Regex%20%3D%20CreateObject%20(%22Vbscript.Regexp%22)%0A%0AWith%20Sheets%20(%22Table1%22)%0A%0A%20%20%20%20%20MaxRow%20%3D%20.Cells%20(.Rows.Count%2C%201)%20.End%20(xlUp)%20.Row%0A%20%20%20%20%20Set%20rngRange%20%3D%20.Range%20(%22A2%22%2C%20.Cells%20(MaxRow%2C%201))%0A%20%20%20%20%0A%20%20%20%20%20lngStep%20%3D%20(MaxRow%20-%201)%20%2F%205%0A%20%20%20%20%0A%20%20%20%20%20With%20regex%0A%20%20%20%20%20%20%20.MultiLine%20%3D%20True%0A%20%20%20%20%20%20%20.Pattern%20%3D%20%22%5C%20n%22%0A%20%20%20%20%20%20%20.Global%20%3D%20True%0A%20%20%20%20%20End%20With%0A%20%20%20%20%20%20%0A%20%20%20%20%20%20%20For%20lngTeil%20%3D%201%20To%20MaxRow%20-%201%20Step%20lngStep%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20meAr%20%3D%20.Range%20(rngRange%20(lngTeil%2C%201)%2C%20rngRange%20(lngTeil%20%2B%20lngStep%20-%201%2C%201)).%20Value2%0A%0A%20%20%20%20%20%20%20%20%20For%20nCount%20%3D%201%20To%20Ubound%20(meAr)%0A%20%20%20%20%20%20%20%20%20%20%20'replace%20line%20break%20with%20nothing%20or%20another%20character%0A%20%20%20%20%20%20%20%20%20%20%20%20meAr%20(nCount%2C%201)%20%3D%20Regex.Replace%20(meAr%20(nCount%2C%201)%2C%20%22%5E%22)%0A%20%20%20%20%20%20%20%20%20Next%20nCount%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20rngRange%20(lngTeil%2C%201)%20.Resize%20(Ubound%20(meAr))%20%3D%20meAr%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20Erase%20meAr%0A%20%20%20%20%20%20%20Next%20lng%20part%0A%20%20%0AEnd%20With%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20I%20was%20able%20to%20help%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I use a script to migrate data into an .xml environment; the first step is to save an Excel sheet containing that dataset as tab-delimited text (this is non-numeric data). In long blocks of text-based data within a cell, I often insert line breaks (Alt + Return). However, in the resulting .txt file, any data after a line break is bumped into the 1st column. I don't remember having this problem with earlier versions of Excel. Is Excel interpreting these line breaks as tabs? Is there a way to keep the line breaks without this column reassignment? Failing that, is there a quick way to delete all the line breaks in a column? Thanks for help with this mystery.

1 Reply

@MWheel65 

Here is a VBA code - untested ... but it should work 

 

Sub example code ()
Dim Regex As Object
Dim meAr ()
Dim nCount &, MaxRow &, lngTeil &, lngStep &
Dim rng range As Range
Set Regex = CreateObject ("Vbscript.Regexp")

With Sheets ("Table1")

     MaxRow = .Cells (.Rows.Count, 1) .End (xlUp) .Row
     Set rngRange = .Range ("A2", .Cells (MaxRow, 1))
    
     lngStep = (MaxRow - 1) / 5
    
     With regex
       .MultiLine = True
       .Pattern = "\ n"
       .Global = True
     End With
      
       For lngTeil = 1 To MaxRow - 1 Step lngStep
        
         meAr = .Range (rngRange (lngTeil, 1), rngRange (lngTeil + lngStep - 1, 1)). Value2

         For nCount = 1 To Ubound (meAr)
           'replace line break with nothing or another character
            meAr (nCount, 1) = Regex.Replace (meAr (nCount, 1), "^")
         Next nCount
        
         rngRange (lngTeil, 1) .Resize (Ubound (meAr)) = meAr
        
         Erase meAr
       Next lng part
  
End With
End Sub

 

 

Hope I was able to help you.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here