Extract 2, 3, 4 or 5-digit number from string

J

Jim

I have a list of URLs where some contain the parameter "categoryid="
with either a 2, 3, 4, or 5 digit number. I need to extract that
number, as an integer, into a neighboring column. I need a routine or
function to help with this - something along the line of making the mid
function smart enough to recognize numbers and adjust the length value
so it only takes the numbers. And if it doesn't find "categoryid",
then leave the cell blank.

TIA,
Jim
 
B

barrfly

Jim,
If the text string is in cell D6 - try this.

=IF(ISERROR(SEARCH("categoryid=",D6)),"",MID(D6,SEARCH("categoryid=",D6)+11,LEN(D6)-SEARCH("categoryid=",D6)+11))

This function assumes that the numbers are on the end of the text
string. if there is a constant text ending - such as ".com" or
something, simply subtract the length of that string ending from the
mid function.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Extract 5 digit number from string 24
Optional argument as Variant not acting like number 4
Get Part of String 1
Extract 5 digit number from a column 7
Page numbering with Merged Document and IF statements 1
numeric value, vba to split out one digit per column 3
Conversion from Number to Word!! Aki 1

Top