Convert HTML to plain text - use Replace? Substitute? Formula? Guidance requested,

Question

Hi,

I am using Excel to format data and upload to several servers.

Most of the servers use HTML but one does not.

So we are trying to convert the HTML to plain text but I have run into some road blocks.

I have figured out how to use REPLACE to convert <BR> and <br/> to "Alt 10" but I need to replace some text to a Carriage Return. 

I have been unable to figure out how to do this.

Original text:

BMW ALTERNATOR E34 1992 525I 140 AMP <BR>Part Number: 12311738351 <BR>Interchange, Fitment and Specification info:   <BR>12311744567 ; 12311738351; 12311738352; 12311738512; 12311738515;<br> 12311744563<Br/>140 AMP<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 320i Automatic Sedan1992, 1993, 1994, 1995<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 320i Manual Sedan1992, 1993, 1994, 1995<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93)<br> 323i Automatic Convertible1997, 1998, 1999, 2000<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 323i Automatic Coupe1997, 1998, 1999<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 323i Manual Convertible1997, 1998, 1999, 2000<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93)<br> 323i Manual Coupe1996, 1997, 1998, 1999<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 325i Automatic Convertible1992, 1993, 1994, 1995, 1996<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 325i Automatic Sedan1990, 1991, 1992, 1993, 1994, 1995, 1996<Br/>BMW<br> 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 325i Manual Convertible1992, 1993, 1994, 1995, 1996<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 325i Manual Sedan1990, 1991, 1992, 1993, 1994, 1995, 1996<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 325is<br> Automatic Coupe1990, 1991, 1992, 1993, 1994, 1995, 1996<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 325is Manual Coupe1990, 1991, 1992, 1993, 1994, 1995, 1996<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 328i Automatic Convertible1994, 1995, 1996,<br> 1997, 1998, 1999, 2000<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 328i Automatic Coupe1994, 1995, 1996, 1997, 1998, 1999<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 328i Automatic Sedan1995, 1996, 1997, 1998<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93)<br> 328i Manual Convertible1994, 1995, 1996, 1997, 1998, 1999, 2000<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 328i Manual Coupe1994, 1995, 1996, 1997, 1998, 1999<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 328i Manual Sedan1995, 1996, 1997, 1998<Br/>BMW<br> 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) M3 Automatic Convertible1997, 1998, 1999, 2000<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) M3 Automatic Coupe1994, 1995, 1996<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) M3 Automatic Sedan1996, 1997,<br> 1998<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) M3 Manual Convertible1997, 1998, 1999, 2000<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) M3 Manual Coupe1993, 1994, 1995, 1996, 1997, 1998, 1999<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93)<br> M3 Manual Sedan1996, 1997, 1998<Br/>BMW 5 Series (E12,E28,E34,E39,E60,E61) 525i Automatic Sedan1990, 1991, 1992, 1993, 1994, 1995, 1996<Br/>BMW 5 Series (E12,E28,E34,E39,E60,E61) 525i Automatic Wagon1991, 1992, 1993, 1994, 1995, 1996<Br/>BMW 5 Series (E12,E28,E34,E39,E60,E61)<br> 525i Manual Sedan1990, 1991, 1992, 1993, 1994, 1995, 1996

What I want to appear in NOTEPAD:

BMW ALTERNATOR E34 1992 525I 140 AMP

Part Number: 12311738351

Interchange, Fitment and Specification info:  

12311744567 ; 12311738351; 12311738352; 12311738512; 12311738515; 12311744563

140 AMP<Br/>BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 320i Automatic Sedan 1992, 1993, 1994, 1995

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 320i Manual Sedan1992, 1993, 1994, 1995

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 323i Automatic Convertible1997, 1998, 1999, 2000

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 323i Automatic Coupe1997, 1998, 1999

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 323i Manual Convertible1997, 1998, 1999, 2000

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 323i Manual Coupe1996, 1997, 1998, 1999

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 325i Automatic Convertible1992, 1993, 1994, 1995, 1996

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 325i Automatic Sedan1990, 1991, 1992, 1993, 1994, 1995, 1996

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 325i Manual Convertible1992, 1993, 1994, 1995, 1996

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 325i Manual Sedan1990, 1991, 1992, 1993, 1994, 1995, 1996

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 325is Automatic Coupe1990, 1991, 1992, 1993, 1994, 1995, 1996

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 325is Manual Coupe1990, 1991, 1992, 1993, 1994, 1995, 1996

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 328i Automatic Convertible1994, 1995, 1996, 1997, 1998, 1999, 2000

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 328i Automatic Coupe1994, 1995, 1996, 1997, 1998, 1999

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 328i Automatic Sedan1995, 1996, 1997, 1998

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 328i Manual Convertible1994, 1995, 1996, 1997, 1998, 1999, 2000

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 328i Manual Coupe1994, 1995, 1996, 1997, 1998, 1999

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) 328i Manual Sedan1995, 1996, 1997, 1998

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) M3 Automatic Convertible1997, 1998, 1999, 2000

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) M3 Automatic Coupe1994, 1995, 1996

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) M3 Automatic Sedan1996, 1997, 1998

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) M3 Manual Convertible1997, 1998, 1999, 2000

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) M3 Manual Coupe1993, 1994, 1995, 1996, 1997, 1998, 1999

BMW 3 Series (E21,E30,E36,E46,E90,E91,E92,E93) M3 Manual Sedan1996, 1997, 1998

BMW 5 Series (E12,E28,E34,E39,E60,E61) 525i Automatic Sedan1990, 1991, 1992, 1993, 1994, 1995, 1996

BMW 5 Series (E12,E28,E34,E39,E60,E61) 525i Automatic Wagon1991, 1992, 1993, 1994, 1995, 1996

BMW 5 Series (E12,E28,E34,E39,E60,E61) 525i Manual Sedan1990, 1991, 1992, 1993, 1994, 1995, 1996

Is there a formula or method to achieve this

Answer 1

You may use Substitute formula in excel.

I copied your Notepad text in cell A1, then give this formula in A3

=SUBSTITUTE(SUBSTITUTE(A1,"<BR>",CHAR(10)),"<Br/>",CHAR(10))

Then I copied cell A3 in MS Word,  it showed it in different lines. Then by copying with Ctrl A from Word and pasting it to Notepad gave me your desired solution.

Answer 2

This would work for a different problem. The HTML coding is already in the text and will re-appear in the CSV as "<BR> or <br/>" . As to removing the formatting - the server I am working with is doing this exact thing. Stripping out the formatting. Whatever I currently upload appears as one long string of text. I am attempting to put back the line breaks and other formatting. I hope to achieve this by converting the HTML <BR> <br/> to new line breaks that this particular server will recognize. I hope that the substitute with CHAR 10 will work.

Answer 3

To answer your questions:

The text I need to replace is the HTML coding that signals a new line. <BR> and <br/>.

I have successfully used REPLACE with a line feed but this particular server does not give me the formatting I am looking for (a complete line break) that a Hard Return or Carriage return or ¶Pilrow would.   From what I have read - Line feed maybe the only option available. 

I hope the solutions below will work.

Answer 4

How about stripping formatting by using SAVE AS to CSV format.

CSV is straight text and any server will happily upload it to almost any program.

Answer 5

So we are trying to convert the HTML to plain text but I have run into some road blocks.

I have figured out how to use REPLACE to convert <BR> and <br/> to "Alt 10" but I need to replace some text to a Carriage Return. 

Your HTML puts the slash for the closing tag at the end of the text? I always thought it went in front of the text. Nevertheless, can you explain what you mean by "but I need to replace some text to a Carriage Return"? What text and how is it different from what you are replacing with "Alt 10" (which is a Line Feed by the way)? More importantly, how are we to know what text gets replaced with which "next line" character?