formula/macro to mimic html-formatting from one cell to another

J

JCrowell

New Member
Joined
Aug 27, 2015
Messages
17
Sorry if this is a repeat question... what I'm trying to do is the following...

If I manually html-format the text in a cell, I want to know if there's a macro/formula/tool for Excel that I can apply that will mimic the html formatting in that first cell, and convert plain text in another cell to also be html-formatted text.

In other words, I have various cells that will have text in them that will need to be html-formatted text. I want to manually html-code the first cell, then be able to copy/paste and apply to other cells with plain text, so that they will also have similar html-coded text.

Any help would be appreciated!

thanks!
 
Sort by date Sort by votes
My mistake. Use [html] your html code [/html] instead. the result does not mess up the html tokens:

HTML: 
<!DOCTYPE html>
<html>
<body style="font-size:16px">

<p>The kbd element represents keyboard input:</p>

<p><kbd>File | Open...</kbd></p>

</body>
</html>

Go Advanced button will allow you to preview your post before it is sent.
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
pbornemeier said:
My mistake. Use [html] your html code [/html] instead. the result does not mess up the html tokens:

Gotcha... see below:

HTML: 
 <p><strong>LGI Technology: LED Retrofit Kit</strong><br><br>Meet our new LED Retrofit kit for HID Lamps. Designed especially for the high heat factor, and active cooling makes it the perfect solution for the fixtures that have a hard time reducing heat.<br><br></p><p>The external driver also causes the LED engine to detach from the heat induced by the driver. Perfect for retrofitting High Bays, Flood Lights, Wall Packs, Street Lights, and more.</p><p> </p><h4>Specifications</h4><br><strong>Features and Benefits:</strong><br><ul><li>Lumen Output: Available in 4000, 5500, 11400, 14000, 17000, 23000, 28000, & 36000</li><li>Consumption (in Watts): Available in 40, 60, 120, 150, 185, 240, 320, & 400</li><li>Direct Voltage : 120V/277V</li><li>Energy saving : Replaces 750W Metal Halide or HPS</li><li>3 Year Warranty : Guaranteed to last 3 years</li><li>UL Certified : UL Certified</li><li>DLC Listed : Design Lights Consortium and utility rebate approved *</li><li>Color Temperature : 5000K</li><li>Active Cooling : Active Cooling with a fan keeps LED’s cool at high temperatures</li><li>Fan : High Quality Ball Bearing Fan rated at 50000 Hour</li><li><br><strong>Commercial:</strong></li><li>High Bays</li><li>Street Lights</li><li>Area Lights</li><l>Wall Packs</l>
</ul>
Click to expand...
 
Upvote 0
I added returns to your post above for readibility.
HTML: 
<p><strong> LGI Technology: LED Retrofit Kit </strong> Meet our new LED Retrofit kit for HID Lamps. Designed especially for the high heat factor, and active cooling makes it the perfect solution for the fixtures that have a hard time reducing heat.</p> 
<p> The external driver also causes the LED engine to detach from the heat induced by the driver. Perfect for retrofitting High Bays, Flood Lights, Wall Packs, Street Lights, and more. </p> 
<p>Â </p>
<h4>Specifications</h4>
<strong>Features and Benefits:</strong>
<ul>
	<li>Lumen Output: Available in 4000, 5500, 11400, 14000, 17000, 23000, 28000, & 36000</li>
	<li>Consumption (in Watts): Available in 40, 60, 120, 150, 185, 240, 320, & 400</li>
	<li>Direct Voltage : 120V/277V</li>
	<li>Energy saving : Replaces 750W Metal Halide or HPS</li>
	<li>3 Year Warranty : Guaranteed to last 3 years</li>
	<li>UL Certified : UL Certified</li>
	<li>DLC Listed : Design Lights Consortium and utility rebate approved *</li>
	<li>Color Temperature : 5000K</li>
	<li>Active Cooling : Active Cooling with a fan keeps LED’s cool at high temperatures</li>
	<li>Fan : High Quality Ball Bearing Fan rated at 50000 Hour</li>
	<li><strong>Commercial:</strong></li>
	<li>High Bays</li>
	<li>Street Lights</li>
	<li>Area Lights</li>
	<l>Wall Packs</l>
</ul>

Now what does the above text look like before formatting? How do you intend to mark (probably with different special characters)
1) Where you want paragraphs to start/end
2) Where you want paragraph/list text to be strong,
3) where a list should start/end
4) The separate items in the list

You could:
* Put a carriage return in at the end of each paragraph or list item
* Surround the bold sections with underscores: _LGI Technology: LED Retrofit Kit_ Meet our new ....
* Put an asterisk at the start of each list item
Like this:
Code: 
_LGI Technology: LED Retrofit Kit_ Meet our new LED Retrofit kit for HID Lamps. Designed especially for the high heat factor, and active cooling makes it the perfect solution for the fixtures that have a hard time reducing heat.
The external driver also causes the LED engine to detach from the heat induced by the driver. Perfect for retrofitting High Bays, Flood Lights, Wall Packs, Street Lights, and more.
Â
_Specifications_
_Features and Benefits:_
*Lumen Output: Available in 4000, 5500, 11400, 14000, 17000, 23000, 28000, & 36000
*Consumption (in Watts): Available in 40, 60, 120, 150, 185, 240, 320, & 400
*Direct Voltage : 120V/277V
*Energy saving : Replaces 750W Metal Halide or HPS
*3 Year Warranty : Guaranteed to last 3 years
*UL Certified : UL Certified
*DLC Listed : Design Lights Consortium and utility rebate approved \*
*Color Temperature : 5000K
*Active Cooling : Active Cooling with a fan keeps LED’s cool at high temperatures
*Fan : High Quality Ball Bearing Fan rated at 50000 Hour
*_Commercial:_
*High Bays
*Street Lights
*Area Lights
*Wall Packs

Code could be written to modify that to near your html format.
That would cover most of your requirements, but not the header 4 requirement for the line above the bulleted list and any "real" asterisk would have to be proceeded by an escape character (say \)

But that still requires new formatting for each cell

If all of the copy had the same number of paragraphs, or the separate types of the copy were in different cells then they could be combined in the format you want.

Another option would be export the cells into a MSWord single-column table, format it there and save it a .html file or if not MSWord some other html formatting program.

Some possibilities:
Text to HTML
Convert text to HTML - Converters
Online Visual HTML > edit html and convert Text to HTML code | Knopok
 
Upvote 0
Hi, Phil, thanks for looking at this... yes, I'm trying to avoid formatting each cell, one at a time, but I'm positive that all the numbers of paragraphs won't match exactly... there is a vague format/template that all text descriptions will follow, but not all, exactly, with the same number of paragraphs...

I'm intrigued by this: Another option would be export the cells into a MSWord single-column table, format it there and save it a .html file or if not MSWord some other html formatting program.

question: once saved in .html file format, could I add html coding to it (I don't know how to format the <h4> coding in regular text - that will have to be added in html mode), then copy and paste all the html-coded text into an Excel column? In other words, can I easily switch back from html code to formatted text mode in MS Word, then copy and paste the final html-coded text into an Excel column?
 
Upvote 0
I forgot how much extra crap MSWord adds to its html output. You might want to consider a different html editor.

I used Online Visual HTML > edit html and convert Text to HTML code | Knopok

to quickly get
HTML: 
<p>
	This is the first paragraph</p>
<p>
	This is the <strong>second </strong>paragraph</p>
<ul>
	<li>
		Bullet 1</li>
	<li>
		Bullet 2</li>
	<li>
		Bullet 3 with a <strong>bold </strong>bold</li>
</ul>
<p>
	<strong>Bolded line at the end</strong></p>

When this pasted into Excel worksheet area it is split into many cells
Select a cell and paste it into the formula editor and it shows up in a single cell
 
Upvote 0
pbornemeier said:
I forgot how much extra crap MSWord adds to its html output. You might want to consider a different html editor.

I used Online Visual HTML > edit html and convert Text to HTML code | Knopok

to quickly get
HTML: 
    This is the first paragraph


    This is the second paragraph


    
        Bullet 1
    
        Bullet 2
    
        Bullet 3 with a bold bold


    Bolded line at the end

When this pasted into Excel worksheet area it is split into many cells
Select a cell and paste it into the formula editor and it shows up in a single cell
Click to expand...

Will give it a go - thanks much, Phil
 
Upvote 0
Hi again, Phil,

Thanks for your direction with this question. I have another somewhat related question - I need a formula or macro to create random 5-digit numbers in a column... what formula/macro can I use?
 
Upvote 0
If you want leading zeros, use =RIGHT("0000" & RANDBETWEEN(0,99999),5)
otherwise =RANDBETWEEN(10000,99999)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Formatting error when switching from Mac to PC
Replies
6
Views
65
RoryA
RoryA
E
  • Question Question
Negative times and conditional formating.
Replies
2
Views
188
Ludwig
L
datatronics505
  • Solved
Absolute reference to a cell of another table
Replies
5
Views
172
datatronics505
datatronics505
D
  • Question Question
FILTER Function Doesn't Display Formatting
Replies
4
Views
1K
diderooy
D
R
  • Question Question
Wrapped text for HTML in excel cell
Replies
3
Views
289
Rhothgar
R

Forum statistics

Threads
1,223,992
Messages
6,175,831
Members
452,673
Latest member
LaMiaAvy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top