How to convert formatted text to html in Excel

[Rude86]

Hi Guys,

Sorry if this has been covered I tried searching but couldn't come up with anything relevant.

I am having trouble finding a script to convert text in an excel cell to format into html.
The reason I need this done is I am importing product descriptions into a program and they only accept CSV format.
When I save the excel doc into csv it loses its formatting and just strings the text as 1 line with no space formatting.

EXAMPLE

Industry Specification A5.1 E 6011 Features -Fast freezing weld metal -Thin flux coating -Deep, forceful arc -Versatile electrode When saving this as CSV it just strings all the text as 1 line. Is there a way of keeping the formatting when saving to csv? Thanks in advance! <tbody> </tbody>

<tbody>
</tbody>

 

[Macropod]

CSV is a text-only format. Font attributes, line breaks within cells, etc. are not retained. It's not clear how you'd expect to get HTML in CSV format - the two are entirely different file formats. If you want to include HTML tags in the CSV output, you'd need to add them to the data before saving as CSV.

 

[Rude86]

CSV is a text-only format. Font attributes, line breaks within cells, etc. are not retained. It's not clear how you'd expect to get HTML in CSV format - the two are entirely different file formats. If you want to include HTML tags in the CSV output, you'd need to add them to the data before saving as CSV.

That's correct!

Is there a way of running a script or something that can add those tags in automatically?
All i basically need is line spacing

 

[mikerickson]

Excel is a spreadsheet, it is not a web designing tool.

Have you tried Save as WebPage

 

[Krayons]

Sub exportCSV()

Dim rng1 As Range
Dim rng2 As Range

' exclude column headers
Set rng1 = Range("A2:B10")
 
Open ActiveWorkbook.Path & "\Export.csv" For Output As #1
 
For Each rng2 In rng1
  Print #1, rng2.Value
Next
 
Close #1

End Sub

Worksheet looks like:

NAME	AGE
BOB	1
SUSAN	2
JIM	3
CAROL	4
CARRY	5
JOHN	6
SMITH	7
APPLE	8
BEAR	9

<colgroup><col width="72" span="2" style="width:54pt"> </colgroup><tbody>
</tbody>

Output is CSV file:

BOB
1
SUSAN
2
JIM
3
CAROL
4
CARRY
5
JOHN
6
SMITH
7
APPLE
8
BEAR
9

<colgroup><col width="72" style="width:54pt"> </colgroup><tbody>
</tbody>

See if you can use this as a starting point.

 

[Macropod]

Is there a way of running a script or something that can add those tags in automatically?
All i basically need is line spacing

I'm not sure what you mean by line spacing. If you want to replace the line-feeds in your posted sample with HTML line break codes, you could use a macro like:

Sub Demo()
Application.ScreenUpdating = False
Dim xlCell As Range
For Each xlCell In ActiveSheet.UsedRange.Cells
  With xlCell
    If InStr(.Text, Chr(10)) > 0 Then
      .Value = Replace(.Text, Chr(10), "< br >")
    End If
  End With
Next
Application.ScreenUpdating = True
End Sub

Note: I've had to insert spaces either side of 'br' because of the way the forum software handles the raw tags - remove those spaces before running the macro!

 

[Rude86]

I'm not sure what you mean by line spacing. If you want to replace the line-feeds in your posted sample with HTML line break codes, you could use a macro like:

Sub Demo()
Application.ScreenUpdating = False
Dim xlCell As Range
For Each xlCell In ActiveSheet.UsedRange.Cells
  With xlCell
    If InStr(.Text, Chr(10)) > 0 Then
      .Value = Replace(.Text, Chr(10), "
")
    End If
  End With
Next
Application.ScreenUpdating = True
End Sub

Ill give the above a go in a bit.

What i mean is the text goes from this:

Industry Specification

A5.1 E 6011

Features



-Fast freezing weld metal

-Thin flux coating

-Deep, forceful arc

-Versatile electrode


TO THIS


Industry SpecificationA5.1 E 6011 Features -Fast freezing weld metal-Thin flux coating-Deep, forceful arc-Versatile electrode

 

[Macropod]

So, are all these data in a single cell, or are they in separate cells? If they were in a single cell, I'd expect the output to already be in your 'to this' format. There is no HTML code in any of that!

The code I posted assumes the data are in a single cell and you want the output to have HTML code applied for the line breaks, thus:

Industry Specification< br >< br >A5.1 E 6011< br >< br >Features< br >< br >< br >< br >-Fast freezing weld metal< br >< br >-Thin flux coating< br >< br >-Deep, forceful arc< br >< br >-Versatile electrode

As you can see, the macro adds the HTML < br > codes (I've had to insert spaces either side of 'br' because of the way the forum software handles the raw tags).

 

[Krayons]

The code i posted here does exactly what you are asking and saves it as a csv file for you. If your data is in one column, then just change this:

Set rng1 = Range("A2:B10")

to this:

Set rng1 = Range("A2:A10")

 

[Rude86]

I'm not sure what you mean by line spacing. If you want to replace the line-feeds in your posted sample with HTML line break codes, you could use a macro like:

Sub Demo()
Application.ScreenUpdating = False
Dim xlCell As Range
For Each xlCell In ActiveSheet.UsedRange.Cells
  With xlCell
    If InStr(.Text, Chr(10)) > 0 Then
      .Value = Replace(.Text, Chr(10), "< br >")
    End If
  End With
Next
Application.ScreenUpdating = True
End Sub

Note: I've had to insert spaces either side of 'br' because of the way the forum software handles the raw tags - remove those spaces before running the macro!

You are a legend!!

I have just tried this and it works great!

Thankyou very much this will save me a lot of time!