convert characters in excel sheet to web-page html

[mdavid]

Hi, I need help to write a vba routine to convert special characters in an excel sheet - such as ampersand (&) , quotes (") and apostrophe (') to their valid html equivalent on a web-page, I also need to replace all CrLf combinations with a space.
Thanks for your help

 

[Chihiro]

Depends on version.
To format regular string into URL encoding...

If using Excel 2013 or later.

Dim urlString As String
urlString = Application.EncodeURL("Some String")

If using earlier version... see link (this is also required if you are using UTF-8 rather than ASCII).
How can I URL encode a string in Excel VBA? - Stack Overflow

 

[mdavid]

Thanks Chihiro,
I'm a little inexperienced in vba, so need a little help applying this.
I'm using excel 2016. Which specific characters do I need to encode for a webpage? and how would I apply the encoding to all rows in the worksheet?
Your help's much appreciated.

 

[Chihiro]

I'd recommend uploading sample workbook if you need further help. With desired output as well.

Other than that, you really don't need VBA for this as it is worksheet function that is called via Application.FunctionName.

Ex: If A1 holds string that you want to convert to url encoded string...
=URLEncode(A1)

 

[mdavid]

Thanks Chihiro