Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 – Stef Jun 5 '12 at 12:35
what regex implementation? vba? notepad++? – Aprillion Jun 6 '12 at 8:34

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, " ")
pobjCell.Value = psCellText
share|improve this answer
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.


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? – Aprillion Jun 14 '12 at 21:23
No need for ?. – Tom Wijsman Jun 14 '12 at 22:59

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):


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

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>"
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
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
one two three

Produces the following output:

the quick brown
 sometext withinmy cell toexport 
fox jumps
overthe lazy
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .