Get text/string from a cell based on it's Nth Ocurence

Basically a Cell has this text written it:

Erick - Code 1 Owner Operated

John - Code 3 Driver

Cell A1 for example: "Erick - Code 1 Owner Operated"

I need to basically get the number in between the strings in the cell. Basically I need to get "1" in Cell A1 (then "3" in Cell A2, etc).

I could use the "SEARCH" feature and have it find the word "Code" since it's a constant and then just add +5 to get the occurence of the numbers.

i.e.

=SEARCH("Code", A1) + 5

Which gives me 16 (which is the location of the character "1" inside the cell).

Is there a way I can tell excel to do the opposite, and just tell it to give me what the character is if I give it it's location inside the cell?

Or is there a better way of finding this?

try this

Please Login or Register  to view this content.

=MID(A1,SEARCH("code",A1,1)+5,1)

edit Judge beat me to it...but you only need 1 character, not 2

try
=LOOKUP(9.99E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))) well that's forgetting the obvious that code is just before the number doh

@DFibbins....very true....I was allowing for CODES up to 99....

@FDibbins....OOPS....sorry bout that...my fingers have a mind of the own....and they're old....

@Judge, yup, thats what I figured too

Glad we could all help....