Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I exported an Excel File to CSV and do have a lot of multi-lines within. I marked the beginning of each line with * and the end with ###.

*** some
text within
my cell to
export ###

Could someone help me with some RexEx Expression to remove the CRLF out of this text file to get it like

*** some text within my cell to export ###
share|improve this question
    
found a different way: please compare answers.yahoo.com/question/index?qid=1005120800381 –  Stef Jun 5 '12 at 12:35
    
what regex implementation? vba? notepad++? –  deathApril Jun 6 '12 at 8:34
add comment

4 Answers

s/[\n\r ]+/ /g

This will globally replace any spaces, carriage returns and newlines) [\n\r ]+ by a space ().


s/\*\*\(*.*)[\n\r ]+(.*)###/$1 $2/g

This is a version of the previous one that suspects your sentence to start with *** and end with ###.


s/^\*\*\*(.*)[\n\r ]+(.*)###$/$1 $2/g

This is a version of the previous one that also makes sure that *** starts at the beginning of a line and that ### ends at the end of the line. One of these should match what you want.


s/MATCH/REPLACE/OPTIONS

is sed syntax, you might want to have just /MATCH/OPTIONS or MATCH and replace it by REPLACE. Depends on how you are planning to use the regular expression. I believe the middle one would work best given your descirption.

share|improve this answer
    
* in sed is a lazy operator by default or did you mean to use .*? instead of .*, so that ***### unwanted \r\n\r\n matches ***### are not replaced? –  deathApril Jun 14 '12 at 21:23
    
No need for ?. –  Tom Wijsman Jun 14 '12 at 22:59
add comment

Did it with a Macro:

Dim pobjCell As Range
Dim plCharCounter As Long
Dim psCellText As String



For Each pobjCell In Selection
psCellText = pobjCell.Text
Do While InStr(psCellText, vbLf) > 0
psCellText = Replace$(psCellText, vbLf, " ")
Loop
pobjCell.Value = psCellText
Next
share|improve this answer
add comment

i have no idea if any real implementation of regex supports variable-length lookbehind, but in theory the regex would look like this (the second argument of a replace function would be a space):

(?<=\*\*\*[^#]*)\n(?=[^#]*###)

in practice, csv parsers do differenciate between new lines inside strings (between double quotes) and new lines between rows, so this should not be an issue...

however, it is possible to mark ends of rows with a special character sequence (e.g. "@@@"), then replace all new lines\n with spaces , then replace @@@ with new lines \n ...

share|improve this answer
    
You don't need lookbehind. –  Tom Wijsman Jun 10 '12 at 20:14
add comment

How about some VBS which takes the input of a CSV file and outputs the file but with lines starting in *** and ending in ### joined together?

Option Explicit
Dim fso : Set fso = CreateObject("Scripting.FileSystemObject")
Dim bStripNewline, sOutput, sLine : bStripNewline = False
If WScript.Arguments.Count = 0 Then
    WScript.Echo "Usage: " & WScript.ScriptName & " <file>"
    WScript.Quit
End If
Dim oFile : Set oFile = fso.OpenTextFile(Wscript.Arguments(0), 1)
Do Until oFile.AtEndOfStream
    sLine = oFile.ReadLine
    If Left(sLine, 3) = "***" Then
        bStripNewLine = True
        sLine = Mid(sLine, 4, Len(sLine))
    ElseIf Right(sLine, 3) = "###" and bStripNewLine = True Then
        bStripNewline = False
        sLine = Left(sLine, Len(sLine)-3)
    End If
    sOutput = sOutput & sLine
    If bStripNewline = False Then sOutput = sOutput & VbCrLf
Loop
oFile.Close
Set fso = Nothing
WScript.Echo sOutput

Save it into a file and run it from the command line in the following way:

cscript //NOLOGO nameofscript.vbs <name of csv file> > <new file>

Sample input file:

the quick brown
*** some
text within
my cell to
export ###
fox jumps
***over
the 
lazy###
dog
one two three

Produces the following output:

the quick brown
 sometext withinmy cell toexport 
fox jumps
overthe lazy
dog
one two three
share|improve this answer
    
Why write a whole script if you can use a short regular expression instead? –  Tom Wijsman Jun 10 '12 at 20:27
    
True. To be honest when I started I thought the answer would be shorter than it became. I'm still unclear how the asker is actually planning to strip out the lines, as a regexp on it's own isn't going to do much - especially when Windows doesn't come with something like sed and Excel doesn't do regexps. Maybe a better way would be a VBA macro in Excel to join the offending cells. –  Richard Jun 10 '12 at 20:48
    
I think he would convert to CSV and then import again. Or... VB can do RegExp, VBA too. –  Tom Wijsman Jun 10 '12 at 20:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.