CODEWITHSUNDEEP
htmlexcelvba
user2812394
user2812394

Reputation: 1

Is there a way to format in excel to turn the text into HTML?

Basically when I enter text in an excel cell, I want this (which will be centered):

Weight:10 lbs
Length: 21"
Width: 14"

to become this in another cell:

<p align="center"><font size="2" face="Arial"></font><br></p><p align="center"><font size="2" face="Arial">Weight:10lbs<br></font><span style="font-family: Arial; font-size: small;">Length: 21"<br>Width: 14"&nbsp;</span></p>

I appreciate any help! Thank you!

Upvotes: 0

Views: 89

Answers (3)

John
John

Reputation: 16007

What about:

Cell A1:  <p align="center"><font size="2" face="Arial"></font><br></p><p align="center"><font size="2" face="Arial">

Cell B1:  Weight:10lbs

Cell C1:  <br></font><span style="font-family: Arial; font-size: small;">

Cell D1:  Length: 21"

Cell E1:  <br>

Cell F1:  Width: 14"

Cell G1:  &nbsp;</span></p>

And the result you want is

Cell H1:  =A1&B1&C1&D1&E1&F1&G1

Rearrange the columns to whatever is convenient, pull out the field names and just use the measurements, etc. ... same idea.

If you already have all three fields in a single cell, then you can split them out into columns easily enough:

  • If your data are in A1, place =SUBSTITUTE(A1, CHAR(10), "|") to get the data separated by vertical lines in B1.
  • Paste B1 by value into C1.
  • Use the Text to Columns tool to separate the fields in C1 into C1 and the adjacent columns, using | as the delimiter.

Upvotes: 1

Daniel Gimenez
Daniel Gimenez

Reputation: 20494

The other two answers are both very valid. This solution does not use VBA. It is similar to John's except that it uses multiple instances of SUBSTITUTE to create the new text from a template.

Assume Your values are in A2-C2 (This is arbitrary and up to your discretion).

Template in $A$1

<p><br></p>
<p align="center"><font size="2" face="Arial">
  {0}<br></font><span style="font-size: small;">{1}<br>{2}" 
</span></p>

Result Text

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1, "{0}", A2), "{1}", B2), "{2}", C2)

The advantage over a method that uses concatenation, is that you can radically change the order in which items appear in the template without having to change the function that produces the result.

Even if your template was changed to the one below, the same function using substitute does not have to be changed, which is convenient if you have a low of rows.

<div>{2}<table><tr><td>{0}</td><td>{1}</tr></table></div>

Upvotes: 1

gavin
gavin

Reputation: 1346

Your requirement is not clear, this is what I have:

Public Sub Change()
Dim a As String
a = Range("A1:A1")
'MsgBox a

Dim resultCell As Range
Set resultCell = Range("A2:A2")
Dim splitVals As Variant
splitVals = Split(a, Chr(10))

resultCell.Value = "<p align='center'><font size='2' face='Arial'></font><br></p><p align='center'><font size='2' face='Arial'>" & splitVals(0) & "<br></font><span style='font-family: Arial; font-size: small;'>" & splitVals(1) & "<br>" & splitVals(2) & "&nbsp;</span></p>"



End Sub

Put your values in first cell with Alt + enter as new line and you will get your result below that.

Upvotes: 0

Related Questions