Turn Excel line break into

Question

A client sent me a huge list of product name and descriptions. The Description cells have text wrap and many line breaks. I need to import this into a MySQL which I do through Navicat Premium.

The problem is that the description cell is used as the HTML description of each product page.

Is there a way to replace Excel's line break with the <br> either in the same Excel file or by a php function?

Answer 1

First make sure you account for both CR and LF which tend to come together. The codes for these are 0013 and 0010 and so you will need a formula that allows you to clean both. I used this formula successfully =SUBSTITUTE(A3,CHAR(13),"<br>") to convert a cell of long text in excel replacing invisible breaks with the 'br' tag. Since you can't tell exactly what kind of line break you have you can also try it with 0010 or =SUBSTITUTE(A3,CHAR(10),"<br>")

Answer 2

A little bit of ASCII coding will go a long way.

Set up the find/replace dialogue (Ctrl-H). In the Find field, hold down the Alt key and type 010 from the numeric key pad. (This lets you find a linefeed character.) In the replace field, put your <br>.

Answer 3

or use a VBA function to replace the carriage returns in a string

Insert a MODULE and paste this

Function LineFeedReplace(ByVal str As String)
dim strReplace as String

strReplace = "<br>"

LineFeedReplace = Replace(Replace(Replace(Replace(Replace(Replace(str, Chr(10), strReplace), Chr(13), strReplace), vbCr , strReplace), vbCrLf, strReplace), vbLf, strReplace), vbNewLine, strReplace)

End Function

If cell A1 contains a string with a linefeed then =LineFeedReplace(A1) will return the string with all linefeeds set to <br>