Forum Discussion

MWheel65's avatar
MWheel65
Copper Contributor
Nov 14, 2020

jumbled column contents excel to .txt

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-base...
excel
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Nov 15, 2020

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

 

Resources