How to extract the street number from an address column and place it in a new column?

Hello,

I'm working with a spreadsheet in Excel 2010 and am trying to extract all of the Street Numbers (all are 3 or 4 Digits) from an Address Column (actual addresses starting in Cell C2), and placing them into a New Column/Cell. However many new Columns I need to create (1 or 2) doesn't matter, I just want them separated:

Original Headers: A1: First Name, B1: Last Name, C1: Address, D1: City, E1: State, F1: Zip Code

Sample Address Data: 5645 Vista Lane

I apologize in advance if this is a simple task, or it was already discussed. I've searched the forum/internet for days, yet regardless what formula I try, I can't get it to work. I'm obviously doing something wrong.

Any help would be greatly appreciated!

if the address always has 3 or 4 digits at the beginning of the cell - then you are going to pick up the space , but a trim can fix that

=LEFT(C2,4) that will extract the 4 digits
then =TRIM(LEFT(C2,4)) will remove the space that 3 digits will have

Thanks Kevin UK and etaf,

I apologize for the delayed reply, but I thank you both for the response.

I thought I was somewhat knowledgeable with Excel formulas, and could at least enter one correctly, but I can't seem to get either solution to work.

I first create a empty/new column after C (I tried before and after Column C), yet no desired result shows up other than the formula I just entered into the Formula Bar(?). Enclosed is a partial/sample Excel worksheet that I am working with.

Thanks,

Sample.xls