How do I separate a single field address line into separate fields for number, Street and unit/Apartment

[Raibert]

I guess that the first issue is i FORGET HOW TO even move the number part of the field to a separate column.
Sample data:

12 HOLBORN DR UNIT A8
12 HOLBORN DR UNIT A8
101 BRADLEY DR
50 HOLBORN DR APT 409
20 CONWAY DR APT 2
39 PENROSE AVE
39 PENROSE AVE
39 PENROSE AVE
39 PENROSE AVE
39 PENROSE AVE
39 PENROSE AVE
5 SIMPSON AVE
65 HOLBORN DR APT 102
7 OLD CHICOPEE DR UNIT 404

LibreOffice 4.2.6.3 English(Canadian) Calc
Ubuntu 14.04 English(Canadian)

[oweng]

Unless the street part of each address has a fairly strict pattern it will not be possible. The usual variability in this type of data (and personal names) means parsing with a regular expression will be of limited success. You may find a basic [0-9]*[ ][a-z]*[ ][a-z]* pattern will work for the street part but it can become more complicated very easily.

Basic solution: Given the address string in cell A1, this formula will extract the street address portion (into cell B1): =IFERROR(LEFT(A1,SEARCH("[ ](APT|UNIT)[ ].*",A1,1)),A1). Note that this will include a trailing space, which can be trimmed (TRIM()) if later desired, but is useful in subsequently extracting the apartment / unit part of the address into cell C1: =SUBSTITUTE(A1,B1,"",1).