"80,000 posts still need to be manually cleaned because they don't follow the convention of any known spreadsheet maker's software. "

[sheetspread]

Bill Jelen's Sternly Written Policy on Posting Your Worksheet to the MrExcel Message Board

Effective January 9, 2020, there are two acceptable ways to post your worksheet to the MrExcel Message Board: (1) Paste a screenshot or (2) post code generated by the official XL2BB.xlam add-in.

www.mrexcel.com

During 2019, when we were planning on converting from vBulletin to Xenforo, converting from HTML to BBCode, we discovered around 200,000 spreadsheets posted to the board using 36 different styles. My goal is to preserve every single contribution because it enriches the community. We had to write code to convert each old style to the new style.

Keep in mind that we had to do all the conversions in one weekend to minimize the time the board was down.

We were able to convert 120,000 posts using the converter code. However, 80,000 posts still need to be manually cleaned because they don't follow the convention of any known spreadsheet maker's software.


Why is Jeanie recognized (albeit without formulas) but not MrExcel HTMLMaker?

 

[smozgur]

Why is Jeanie recognized (albeit without formulas) but not MrExcel HTMLMaker?

Most of the HTML Maker posts were also recognized and auto-converted. However, members created their own table tags or modified HTML Maker tags for their visual preferences and this caused different patterns that cannot be easily recognized by Regex as we used to auto-convert those HTML content. Since the priority was upgrading the forum software on November, we postponed fixing those out-of-pattern ones. However we are still working on those remaining and those will be all fixed.

 

[sheetspread]

members created their own table tags or modified HTML Maker tags for their visual preferences and this caused different patterns that cannot be easily recognized

Ok thanks that makes sense. I downloaded mine from Smitty's site but I guess he was supercharging the files and not telling anyone

 

[smozgur]

It is not necessarily to be supercharged, but certain tags might have been altered that we used as signs to catch during the conversion. We will handle all of them until all posts are converted, it only takes some time. We've been working on the XL2BB updates recently, but hopefully it is so close to release XL2BB v1.2 now. So we will resume fixing the old content as soon as possible.

Thanks for your inquiry which is giving us chance to provide more information on this matter.

 

[sheetspread]

Any updates on this or ways I can help?

 

[smozgur]

Yes, many of them converted since then, and there are still 50K posts to be converted which is less than 1% of the total post count in the Excel forum and they have mostly customized HTML that makes auto-conversion impossible. If you have specific posts that you are trying to access urgently but can't view them because of the garbage HTML, then you can tell us the post links (of course I don't mean hundreds of links here) and we can try to manually convert them like we've been already doing when we receive individual requests like this.

Or, if you need to read a post like this urgently, then you can also do that by yourself by using the following HTML beautifier to see the HTML content that you need:

Best HTML Viewer, HTML Beautifier, HTML Formatter and to Test / Preview HTML Output

Online HTML Viewer, HTML Beautifier, HTML Formatter, HTML Editor to Test output - Convert HTML Strings to a Friendly Readable Format, Beautify.

codebeautify.org

 

[sheetspread]

So what's easiest for you? If I go to the posts with garbled html and add a new entry in the readable format? Or send you an Excel file with the tables and links/post # they're supposed to replace?

 

[smozgur]

I surely meant a couple of links that you might urgently need to access their content, not an Excel worksheet with tens/hundreds of posts. So, if you post a couple of links here, then we'll do our best to convert them to XL2BB tags and edit those posts.

Note: You can copy the post link by using the share icon on the right corner of the post:

 

[sheetspread]

Ok thanks I'll send just a few links soon.

 

[sheetspread]

VBA to sumproduct & loop...I think

Hi all, Sheet1 has product Bill of Materials: Sheet2 is for user to input Product & Tonnes in the INPUT section: I'm looking for VBA to calculate & return Ingredients & total Tonnes under the RESULT section. Thanks for any help in advance. Mark

www.mrexcel.com

Index Match On Multiple Columns

Hi and good evening,how would I perform a lookup on 1 unique "date time value" in columns D and F and return their matches from columns C or E all sorted from oldest to newest (like finished Data Set below).Times and Dates in columns D and F are formatted as "ddd-dd-mmm-yy hh:mm".Also I would...

www.mrexcel.com

Flag Backorder Shipments

Hi Everyone, I'm trying to get a cleaner formula to label any records shipped as backorder. Here's my formula, but know there has to be something cleaner. =IF(AND(B2=B1,C2C1),"Backorder",IF(AND(A1="Backorder",B2=B1,C2=C1),A1,"Regular")) Formula: if Order# is the same as previous Order#, and...

www.mrexcel.com

Are three. No hurry.