need help with showing the FULL hyperlink as a value for the link address instead of in link form

[kbishop94]

my code stops (wont go beyond) displaying the link's address when it hits the "#" character... I am wanting it to display the entire link... Thanks for any help.

Yellow Highlight in cell R2 shows what the entire Hyperlink address is supposed to say...
Blue = my code
Red indicates where the code stops and wont copy the hyperlink address beyond that of the "#" character that is encountered in the address.

 

[kbishop94]

my code stops (wont go beyond) displaying the link's address when it hits the "#" character... I am wanting it to display the entire link... Thanks for any help.

Yellow Highlight in cell R2 shows what the entire Hyperlink address is supposed to say...
Blue = my code
Red indicates where the code stops and wont copy the hyperlink address beyond that of the "#" character that is encountered in the address.
View attachment 103831

this is not an issue (near as I can tell) with it exceeding 256 characters, btw (link in the example has 93)

just wanting to make the code to return THIS:
http$://otis.osmanager4.com/SeatexLLC#/chemical/material/0028438d-2166-4db0-947d-becc76ae1294

Instead of what it currently does which is THIS:
http$://otis.osmanager4.com/SeatexLLC

(but not with " $ "... I had to use a different character so this post wouldn't show the actual link.)

any suggestions? Thanks so much