RegEx to Remove CRLF from CSV export from Excel

microsoft-excel regex csv

6,242

Solution 1

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

Solution 2

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.

6,242

Stef

Updated on September 18, 2022

Comments

Stef almost 2 years
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 ###
```
- Stef about 12 years
  
  found a different way: please compare answers.yahoo.com/question/index?qid=1005120800381
- Aprillion about 12 years
  
  what regex implementation? vba? notepad++?
Tamara Wijsman about 12 years

You don't need lookbehind.
Tamara Wijsman about 12 years

Why write a whole script if you can use a short regular expression instead?
Richard about 12 years

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.
Tamara Wijsman about 12 years

I think he would convert to CSV and then import again. Or... VB can do RegExp, VBA too.
Aprillion about 12 years

* 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?