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

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
|
|
|