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

  • Thread starter Thread starter Canuck
  • Start date Start date
C

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