Remove Civic numbers in Street Address, move to previous blank cell

[Canuck]

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

 

[Niek Otten]

The number:

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

The streetname:

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


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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