Formula to Extract Number Prior to .htm in URLs

[powers17]

Hi, I have a list of several hundred URLs that end with a product number. For example, they all end with /18442.htm. I need to pull out all the product numbers into a separate column using a formula. The product number is not necessary the only number in the URL string, but in most cases they are 5 characters long and always precede the .htm. Thanks for any help!

 

[JoeMo]

Excel Workbook
	A	B
1	URL	Prod #
2	5021xyz/abc17345/18442.htm	18442
Sheet5

 

[powers17]

Thank you! I never would have got this on my own. Much appreciated.

 

[JoeMo]

Thank you! I never would have got this on my own. Much appreciated.

You are welcome - thanks for your reply.

 

[Rick Rothstein]

I know I am late to the party, but I think this slightly shorter formula will also work for you...

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",300)),300)),".htm","")