Is it possible to write a formula in Excel to convert a Column (Text) to HTML format?

[Jlr18]

Hello,

I wanted to reach out to see if it was possible to write a formula/function in VB to read and change the entire contents of a cell from Text to HTML format. Reason I asked is because I need to data load about 96,000 records in a CSV file, but in order to keep the format integrity/spacing/bullets etc. I have to convert it to HTML and then perform the data load.

Below is an example.

Any suggestions is greatly appreciated. 

ID for the Data Load	Analysis (TEXT)	What I need it to convert to
a3334000000OIUH	7.2.6 XM -customer reports that certain events are not appearing in Web Console dashboards -target logs not present in PM investigation -explained in depth the difference between logs and events, how GLPRs work, and the difference between ARM and AIE alarming criteria -customer's RBP threshold is 5 (set by Optiv). I recommended raising this to 20. -assisted customer with creation of 2 GLPRs. We did one using the GLPR wizard and another using the 'Create GLPR' button in Log Viewer -the customer had numerous questions along the way. We discussed how to check on the contents of a list, how lists get updated, and what the Threat Intelligence Service is -ultimately, the system was working as designed but the customer really needed training	<p>7.2.6 XM</p> <p>-customer reports that certain events are not appearing in Web Console dashboards</p> <p>-target logs not present in PM investigation</p> <p>-explained in depth the difference between logs and events, how GLPRs work, and the difference between ARM and AIE alarming criteria</p> <p>-customer's RBP threshold is 5 (set by Optiv). I recommended raising this to 20.</p> <p>-assisted customer with creation of 2 GLPRs. We did one using the GLPR wizard and another using the 'Create GLPR' button in Log Viewer</p> <p>- the customer had numerous questions along the way. We discussed how to check on the contents of a list, how lists get updated, and what the Threat Intelligence Service is</p> <p>-ultimately, the system was working as designed but the customer really needed training</p>

[#2 · Accepted solution]

@Jlr18 

Perhaps some approximation will be

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

[#3]

@Sergei Baklan - Thanks so much!!:D I was able to update my spreadsheet and used your formula to research the SUBSTITUTE capabilities. 

Ultimately, I came up with the formula below. I appreciate it very much!

="<p>"&SUBSTITUTE(SUBSTITUTE(A1,CHAR(10),"</p>"&CHAR(10),1),CHAR(10),CHAR(10)&"<p>",2)&"</p>"

[#4]

@Jlr18 , glad to help, you are welcome

[#5]

@Sergei Baklan Thanks so much for your help! had a follow-up question if you had time at some point.

There are some scenarios where the data didn't render with the formula provided. 70% of my spreadsheet looks to be accurate I still have several 1000 to go. 

Here's my question, the formula I used, and an example of some data I am having issues with

Formula: 

="<p>" & SUBSTITUTE(G10, CHAR(10)&CHAR(10),"</p>" 

& CHAR(10)&CHAR(10) & "<p>") & "</p>"

Analysis Field (TEXT)	What the formula rendered	How the data displays with no line breaks. Each number should have a new line break.
1) verified AV exclusions are in place - one AV was good, the other needed them added 2) restarted AI Engine 3) the above steps did not help, scheduled a meeting 4) reviewed process monitor, don't see any other apps touching the directory 5) increased MaxLogDataSize, bounced Engine, ComMgr and AIEDP 6) DataInputProvider cannot dispose of 105153 input files - please check file and folder permissions 7) put Engine and ComMgr in Verbose, no other logs to help 8) files are building up in the Data Error directory, cut and paste those into Data and they were consumed 9) no .dat backlog 10) searched Jira and Salesforce, no good results so far	<p>1) verified AV exclusions are in place - one AV was good, the other needed them added 2) restarted AI Engine 3) the above steps did not help, scheduled a meeting 4) reviewed process monitor, don't see any other apps touching the directory 5) increased MaxLogDataSize, bounced Engine, ComMgr and AIEDP 6) DataInputProvider cannot dispose of 105153 input files - please check file and folder permissions 7) put Engine and ComMgr in Verbose, no other logs to help 8) files are building up in the Data Error directory, cut and paste those into Data and they were consumed 9) no .dat backlog 10) searched Jira and Salesforce, no good results so far</p>	1) verified AV exclusions are in place - one AV was good, the other needed them added 2) restarted AI Engine 3) the above steps did not help, scheduled a meeting 4) reviewed process monitor, don't see any other apps touching the directory 5) increased MaxLogDataSize, bounced Engine, ComMgr and AIEDP 6) DataInputProvider cannot dispose of 105153 input files - please check file and folder permissions 7) put Engine and ComMgr in Verbose, no other logs to help 8) files are building up in the Data Error directory, cut and paste those into Data and they were consumed 9) no .dat backlog 10) searched Jira and Salesforce, no good results so far

Thanks for your time and any suggestions!

Regards,
Jeremy

[#6]

@Jlr18 

Jeremy, I'd use 

="<p>"&SUBSTITUTE(G10,CHAR(10),"</p><p>")&"</p>"

In attached file resulting text is in I10 and its value in K10.

I tested it here https://www.w3schools.com/html/tryit.asp?filename=tryhtml_paragraphs2, text shown as

If you don't need extra blank lines, when

="<p>"&SUBSTITUTE(G10,CHAR(10),"<br>")&"</p>"

(next two cells) and

[#7]

@Sergei Baklan - Again thank you so much for your time!!! This is very helpful! I think I have what I need.  I will work with this formula and adjust as needed.