Скачать или смотреть How to Convert Unusual String Formats to Date in VBA

Learn how to handle and convert strange date formats scraped from the web into usable date formats in VBA.
---
This video is based on the question https://stackoverflow.com/q/68548866/ asked by the user 'Jirka Čep' ( https://stackoverflow.com/u/10542019/ ) and on the answer https://stackoverflow.com/a/68549589/ provided by the user 'Алексей Р' ( https://stackoverflow.com/u/15035314/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: VBA convert unusual string to Date

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Convert Unusual String Formats to Date in VBA

When you are scraping data from websites, you might encounter some unusual string formats that complicate your efforts. One such example is the date format found on Yahoo Finance, which can appear as ?10? ?Aug?, ?2020. The question marks aren’t any typical characters, which can lead to confusion and errors when you try to process them in VBA. This guide will break down how to overcome this challenge and convert those strange date strings into a usable date format.

Understanding the Problem

During your web scraping project, you might pull dates that contain non-printable or odd characters. In this case, the string ?10? ?Aug?, ?2020 is not immediately recognizable or convertible due to these strange symbols. Attempting to convert this string to a date using the CDate() function results in a "Type mismatch" error, rendering your efforts futile.

To fix this issue, you have two main approaches: identifying and removing these strange characters or converting the weird string format directly into a date format.

Solution Breakdown

Approach 1: Using Regular Expressions (RegExp)

One effective way of handling such strings is by utilizing VBA's Regular Expressions. Below, I will outline a straightforward function that extracts the day, month, and year from the input string.

Step 1: Set Up Regular Expressions

Make sure to enable the Microsoft VBScript Regular Expressions 5.5 reference in your VBA project. You can do this from the Tools menu in the VBE (Visual Basic for Applications).

Step 2: Create the Function

Here’s a function GetDate(txt) that processes the unusual string:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Testing the Function

To see the function in action, you can use the following subroutine:

[[See Video to Reveal this Text or Code Snippet]]

Example Output

Running the above code will return the expected date formats:

[[See Video to Reveal this Text or Code Snippet]]

Approach 2: Enhanced Function for Additional Formats

The second function, GetDate2(txt), provides an enhanced version that accounts for other formats.

[[See Video to Reveal this Text or Code Snippet]]

Example Usage

Similar to the first function, you would test it in the Usage2 subroutine:

[[See Video to Reveal this Text or Code Snippet]]

Output Example

Running this subroutine gives you:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

Converting unusual string formats to dates in VBA may seem challenging, but with the power of Regular Expressions, you can tackle this problem effectively. By following the methods outlined in this post, you can confidently scrape data from various formats without worrying about "Type mismatch" errors. Happy coding!