Move numbers end of the cell in new cel

[jellevansoelen]

Hi,

I have a lot of this cells:

Book3
	A
1	Middelweg 55
2	Torenstraat 39-41
3	Nieuwe Steeg 7
4	Schoutlaan 9
5	Leerdamseweg 22a
Sheet1

The numbers in the cell are house numbers.
Now I want pick that housenumber and move that to another cell

But the house numbers can look as 11-13 or 12b.

Exactly i look for a formule what move everything beginning by the first number in that cell.

Result must be:

Book3
	A	B
1	Middelweg	55
2	Torenstraat	39-41
3	Nieuwe Steeg	7
4	Schoutlaan	9
5	Leerdamseweg	22a
Sheet1

Tnx a lot.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1
2	Middelweg 55	Middelweg	55
3	Torenstraat 39-41	Torenstraat	39-41
4	Nieuwe Steeg 7	Nieuwe Steeg	7
5	Schoutlaan 9	Schoutlaan	9
6	Leerdamseweg 22a	Leerdamseweg	22a
Result

Cell Formulas
Range	Formula
B2:B6	B2	=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",100),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),100))
C2:C6	C2	=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),100))

 

[jellevansoelen]

Perfect!
Tnx!

 

[Fluff]

You're welcome & thanks for the feedback.