Extracting 4 digit number from combobox, looking that up in a worksheet column, and then copying 3 columns from the corresponding row to new page.

[bensdb]

I have a combobox called 'cmbkids' on a form named 'Hideform'. On the press of a button I want to search for a 4 digit number within the string that is currently selected in the combobox. The string might look something like this 'Adam,Cordliffe - 1301 - Round 2', but might also sometimes look like this '1301 - Adam,Cordliffe - Round 2' so the position of the 4 digit number is not fixed.

Once it has found the 4 digit number I need to then lookup that number in column A of worksheet 'KidsDB', and then copy columns A to C of that row and paste to the next available row in another worksheet named 'Register'.

I'm really stuck at this point in my project and any help would be appreciated.

Even if you only have part of the solution please point me in the right direction.

Thanks Guys!!

 

[Drrellik]

{=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))} CTRL+SHIFT+ENTER this a found on another site for stripping the numbers out of a text string. hope this points you in the right direction.

 

[bensdb]

Thanks but I'm not sure it will in my case because I have two sets of numbers. I need to get just the 4 digit number!

 

[bensdb]

MsgBox "Number found = " & Application.Evaluate("MATCH(1,--ISNUMBER(FIND(ROW(1000:9999),""" & cmbkids.Value & """)),0)+999")

Right I've now got this bit of cod which extracts a 4 digit number. Now I just need to figure out how to look up that variable in column a of my worksheet, and i think I can figure out the rest myself . vba doesn't have a 'lookup' function does it? What's is equivalent?