separate the number component from the street name in an address list in excel

gregfthompson
gregfthompson used Ask the Experts™
on
Each row in the list contains unit/street numbers with a street name. This request is to separate each existing row into two different columns - one column containing the number component and the adjacent column containing the text component.
File is attached.

Thanking you in anticipation.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try to attach again

Author

Commented:
Sorry. Have had difficulty attaching using Firefox.  Didn't realise it wasn't attached.
street-address-list.xls
For the number
=LEFT(D2,FIND(" ",D2))

for the rest
=SUBSTITUTE(D2,H2,"")
I entered the number part in H2 thus the H2 in the formula for the rest. If you enter the formula for the number part in any other column then you can adjust the formula for the rest accordingly.

Author

Commented:
Thank you heaps!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial