Is it possible to find the first string within a string that contains only numbers/digits? I have a few thousand addresses to process and some of them have Apartment numbers. I need to find the address number and extract it but the apartment number get extracted instead.
I was able to extract the first number string with this Formula, OneToZero is {1,2,3,4,5,6,7,8,9,0}
=MID($A1,MIN(IFERROR(FIND(OneToZero,$A1),"")),FIND(" ",$A1,MIN(IFERROR(FIND(OneToZero,$A1),"")))-MIN(IFERROR(FIND(OneToZero,$A1),""))-0)
Returns this.
1417 Horne RD #1445A (Building 15) ----- 1417(Correct)
Westwood #104A 801 Cantwell Ln --------- 104A(wrong) 801(Correct)
I tried using
=MID($A1,MIN(IFERROR(FIND(" "&OneToZero," "&$A1),"")),FIND(" ",$A1,MIN(IFERROR(FIND(" "&OneToZero," "&$A1),"")))-MIN(IFERROR(FIND(" "&OneToZero," "&$A1),""))-0)
But returns this if the first number doesn't have a non-digit character in front.
Westwood 104A 801 Cantwell Ln --------- 104A(wrong) 801(Correct)
Cannot just remove the first string using the "#" due to it may say "Apt#" or just "104A".
Thank you.
=IF(AND(ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))),A1,"")
I Placed the Formula to find it in place of A1. I will post in the answers section.