Formatting cell with line breaks

[Bill N]

I'm challenged with formatting cells that have line breaks. I'd like t
do the following:

The current cell has the following:

AAA
BBB

I'd like to format this so that it becomes:
<li>AAA</li>
<li>BBB</li>

Does anyone know how how to add a character(s) to the end of each lin
instead of just one character(s) to the end of all text in the cell?

Thanks!

Bil

 

[Jason Morin]

Assuming only 1 line break per cell:

="<li>"&LEFT(A1,FIND(CHAR(10),A1)-1)&"<li>"&CHAR(10)
&"<li>"&MID(A1,FIND(CHAR(10),A1)+1,255)&"<li>"

and format the cell to wrap text.

HTH
Jason
Atlanta, GA

 

[Bill N]

Excellent - thanks Jason.

How would I modify or copy this formula for multiple cells when th
number of line breaks vary with each cell? I could obviously do i
manually, but there must be an easier way to "count" the number of lin
breaks so that the formula can be modified appropriately??

Thanks again for the help...this really saves me a lot of time.


Bill

 

[Jason Morin]

I should have thought of this earlier:

="<li>"&SUBSTITUTE(A1,CHAR(10),"<li>"&CHAR(10)&"<li>")
&"<li>"

This will work for any # of line breaks in the cell. Don't
forget to wrap the text under Format > Cells.

HTH
Jason
Atlanta, GA