finding the first Number in a cell

[vhicks]

Hello,
Is there a formula I can use to find the first number in a cell?

for example:

cell B14 - Alfonse and AliceLeto5803 Breezehill RoadEast SpartaOH44606

I have 200 rows like this where the name runs into the first number of the address,

I want to separate First Name, Last Name, Address, City, State, Zip

I've searched YouTube with no luck,

any help would be appreciated

Thanks,

[63falcondude]

This will pull out everything to the right of (and including) the first number:

=MID(B14,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B14&"0123456789")),LEN(B14))

[sandy666]

try

B14: =MID(B14,MIN(IF(ISNUMBER(--(MID(B14,ROW(INDIRECT("1:"&LEN(B14))),1))),ROW(INDIRECT("1:"&LEN(B14))),LEN(B14)+1)),4) array entered and drag down

I assume your number has lenght 4 always

[vhicks]

No, Sandy, sorry I didn't specify that. they consist of 3-5 digits.

I tried the formula but got no results, or not the one I wanted,, I though it might be something simple like finding the first space - as =FIND(" ",B14) but I see it's not that way

I attached the work book if you'd like to see

[vhicks]

thanks falcondude, that did it. I appreciate ya..!!!

[63falcondude]

You're welcome. Happy to help!

[sandy666]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.