Wow, lots of issues, my friend. Well, you'll have to "build" a solution that solves each problem. So far the needs are
a) remove all values from the decimal and on from a cell value
b) strip out any non-numeric values from the cell too
Ok, a simple LEFT() formula takes all the value before a decimal:
=LEFT(D4,FIND(".",D4)-1)
But that returns an error if there is no decimal, so we have to test for that, too:
=IF(ISERROR(SEARCH(".",D4)),D4,LEFT(D4,FIND(".",D4)-1))
Now, you need a function to search the remaining value from that formula and strip out all the non-numerics. I use a UDF for that called CleanAll. Here's the code for the new function:
This UDF is pretty flexible, you can see some another option commented out in the code that would change the function to stripping out numbers...worth playing with in your free time.
Anyway, add that code into a standard module (same place as regular macros) and your new function CleanAll() is ready to use.
The final formula to insert into the last one above would look like this:
=IF(ISERROR(SEARCH(".",D4)),CleanAll(D4),CleanAll(LEFT(D4,FIND(".",D4)-1)))
Now, all of that inserted into your URL formula (ugh) would look like:
=HYPERLINK("http://www.website.com/registry_images/" & (C4) & "/" & (G4) & "/" & (C4) & "_" & (IF(ISERROR(SEARCH(".",D4)),CleanAll(D4),CleanAll(LEFT(D4,FIND(".",D4)-1)))) & ".jpg")
You might have to play with that a little. Sample book attached showing explanation.
Bookmarks