Extract data from cell starting from 1st number digit to end

[DaanDean]

Hi all,

I want to extract data from a cell ( text and numbers combined / streetnames etc) , but it should only extract from the first number in the textline till last character.

This, because I want to extract all the street-numbers and I cannot use a formula to extract the last word/combination of the cell, because of the occasional spaces involded (example: ....11B ; 11 B; 11 B-C)

See example in attachment.

Thanks!

[JohnTopley]

Try

in B2

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

[Tony Valko]

Try this...

Data Range

	A	B
1	STREETS	Goal is:
2	CORNELIS KRUSEMANSTRAAT 11 HS	11 HS
3	KERKENBOS 1236 C	1236 C
4	WEVERSTRAAT 13 B	13 B
5	KANAALSTRAAT 81 BIS	81 BIS
6	ZIJLMAKERSTRAAT 31 A	31 A
7	VAN DER TAKSTRAAT 1A & 1F	1A & 1F
8	HOGE RIJNDIJK311 B	311 B

This formula entered in B2 and copied down:

=TRIM(MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890)),50))