The number:

=VALUE(LEFT(A1,FIND(" ",A1)-1))

The streetname:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Canuck" wrote in message ...
| How can I take a cell with a street address (345 Beaverbrook Avenue) and
| remove the "345", and place it the previous blank cell in order to split the
| civic number and street name into separate cells for sorting purposes?
|
| So |345 Beaverbrook Avenue| becomes |345|Beaverbrook Avenue|.
|
| Probably easy when you know how. Thanks.
|
| RT
|
|
|