Check hyperlinks in excel - return "Y" or "N"

Hi,
I have a long spreadsheet (1,000+) with links and having trouble because I'd like a formula (or some sort of macro) that returns "Y" in column B if the link is actually broken or "N" if it's not without actually having to click on 1,000+ links. I've seen similar solutions on the web but when applied to my scenario, ALL cells return as broken when I know for a fact they're not.

I'm attaching a sample that contains partial links, here. Please advise? Your help is much appreciated.

Best,
~S

B2=HYPERLINK(A2,A2)
Try this and copy towards down

Ignore my earlier post