Copy and Paste to remove HTML soft hyphen

[AccountingCop]

I am copying a payment confirmation number from a web page
Conf code.png

When I right click and paste to match destination format it pastes into my spreadsheet with a soft hyphen and looks like this:
conf formatting.png

The HTML code on the web page is this:

shy code.png

If i paste the same value into the notepad it pastes the hyphen and I can delete it and then paste it back into Excel but that is time consuming.

I need those numbers to bounce against another spreadsheet to confirm that the confirmation numbers are valid.

Paste Special of the worksheet class will remove html but I am getting a runtime error 1004.



I just got back into vba after five years off and my html is next to nothing.

Please be kind!

[Pepe Le Mokko]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Leith Ross]

Hello AccountingCop,

The following code will work on 32 bit versions of Office, which is what I assume you have. If you have a Mac then you are out of luck since the Mac does not support this code.

Add a new Standard VBA Module to your workbook's VBA Project. Copy all of the code in the attached Notepad file using Ctrl+C and then paste it into your workbook's VBA module with Ctrl+V. Save your changes.

After you have copied the web data to the clipboard, select the a cell on the worksheet where you want to paste the information. Run the macro CleanHTML. You can select the macro from the Macro Dialog. Use the keys ALT+F8 to display it. This will remove the Soft Hyphen tags from all of the HTML code that you copied to the clipboard. The macro will then paste the rendered HTML data to the worksheet's ActiveCell.

NOTE: Do to forum system security, I cannot post this code directly. That is why I have attached the text file.

Macro Code in Attached Text File