Forum Discussion
Is it possible to write a formula in Excel to convert a Column (Text) to HTML format?
SergeiBaklan - 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>"
Jlr18 , glad to help, you are welcome
SergeiBaklan 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
😎 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
😎 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 😎 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,
JeremyJeremy, 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
SergeiBaklan - 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.