Remove text after first occurence of numbers

[ausswe]

I have a large dataset with street addresses (in column A) than can look like this:

• Streetname 15 (this is the street name and number of the street)
• Streetname 16 A (street name + number + entrance)
• Streetname 17 C Apt 1001 (streetname + number + entrance + apartement number)
• Streetname 18 Apt 1021 (streetname + number + apartement number)

I need to match these from another dataset where the streetnames are entered only as "Streetname 15" (with no entrance or apartement number) so I'm trying to find a formula that will remove any text after the first occurence of a numeric value (but keep the number) so that it become:

• Streetname 15
• Streetname 16
• Streetname 17
• Streetname 18

I have tried a few different ways but haven't found one that produces the result.

Any suggestions (and hope that makes sense)?

 

[kvsrinivasamurthy]

A2:A5 your Data
In B2

=LET(a,A2:A5,b,FIND(" ",a,FIND(" ",a)+1),z,LEFT(a,b),z)

 

[saboh12617]

Hello,

You can use a REGEX like so

=REGEXEXTRACT(A1, "^([A-Za-z\s]+)\s(\d+)")

 

[Peter_SSs]

Another RexEx suggestion in B2 with comparisons of the previous suggestions in cols C & D.

25 03 19.xlsm
	A	B	C	D
1		Post 4	Post 3	Post 2
2	Smith Street 15 (this is the street name and number of the street)	Smith Street 15	Smith Street 15	Smith Street
3	Broadway 16 A (street name + number + entrance)	Broadway 16	Broadway 16	Broadway 16
4	Lower Hill Lane 17 C Apt 1001 (streetname + number + entrance + apartement number)	Lower Hill Lane 17	Lower Hill Lane 17	Lower Hill
5	Box-Hill Parade 18 Apt 1021 (streetname + number + apartement number)	Box-Hill Parade 18	#N/A	Box-Hill Parade
RegEx

Cell Formulas
Range	Formula
B2:B5	B2	=REGEXEXTRACT(A2:A5, "^\D*\d+")
D2:D5	D2	=LET(a,A2:A5,b,FIND(" ",a,FIND(" ",a)+1),z,LEFT(a,b),z)
C2:C5	C2	=REGEXEXTRACT(A2, "^([A-Za-z\s]+)\s(\d+)")
Dynamic array formulas.

 

[Peter_SSs]

.. if you do not yet have the REGEX functions then you could try this

25 03 19.xlsm
	A	B
1
2	Smith Street 15 (this is the street name and number of the street)	Smith Street 15
3	Broadway 16 A (street name + number + entrance)	Broadway 16
4	Lower Hill Lane 17 C Apt 1001 (streetname + number + entrance + apartement number)	Lower Hill Lane 17
5	Box-Hill Parade 18 Apt 1021 (streetname + number + apartement number)	Box-Hill Parade 18
Extract

Cell Formulas
Range	Formula
B2:B5	B2	=LET(s,SEQUENCE(10)-1,LEFT(A2,FIND(" ",A2,MIN(FIND(s,A2&s)))-1))

 

[ausswe]

Thanks guys!
I wasn't able to use regexextract apparently but the formula

LET(a,A2:A5,b,FIND(" ",a,FIND(" ",a)+1),z,LEFT(a,b),z)

worked!

 

[kvsrinivasamurthy]

Please use this corrected formula, because previous formula will have a unwanted space at the end.

=LET(a,A2:A5,b,FIND(" ",a,FIND(" ",a)+1)-1,LEFT(a,b))

 

[Peter_SSs]

I wasn't able to use regexextract apparently but the formula

LET(a,A2:A5,b,FIND(" ",a,FIND(" ",a)+1),z,LEFT(a,b),z)

worked!

If that formula (or the post #7 correction which removes the trailing space character) works for you then there must always only be one word before the first numbers. If that is the case then you could use this much simpler formula to extract the results.

ausswe.xlsm
	A	B
1
2	Streetname 15 (this is the street name and number of the street)	Streetname 15
3	Streetname 16 A (street name + number + entrance)	Streetname 16
4	Streetname 17 C Apt 1001 (streetname + number + entrance + apartement number)	Streetname 17
5	Streetname 18 Apt 1021 (streetname + number + apartement number)	Streetname 18
6
Extract (2)

Cell Formulas
Range	Formula
B2:B5	B2	=TEXTBEFORE(A2:A5," ",2)
Dynamic array formulas.