Address calc question ...

[SignExpress]

Okay, here's an issue that I'm sure has been resolved before, but I've search through the forum and can't find anything. How would I break an address in one cell into 3 cells?

EX:

A1 = 6403 VANDERBILT ST

Trying to get:

A1 = 6403
B1 = VANDERBILT
C1 = ST

another wrinkle is that some streets are BLVD, or AVE (or no type!)

[RoryOF]

The method is to write three formulae using the built in functions of Calc, in three adjacent cells. The first inserts the string up to the first space, the second starts at the first space and inserts the centre section of the string up to the second space, the third starts at the second space and inserts the righthand portion of the string.

You will need to consider the case where there might be two or more spaces in the gaps. There may also be other boundary conditions.
The functions you want are Left(), Mid() and Right().

[SignExpress]

What if the street has several words in it? (like 2500 WEST SAM HOUSTON PARKWAY NORTH, ie)

[Villeroy]

Select column with address data.
Data>Text to columns...
<Space> as delimiter.

[RoryOF]

What if the street has several words in it? (like 2500 WEST SAM HOUSTON PARKWAY NORTH, ie)

You have to sort out a protocol for this. Computers cannot do all the thinking for you. Usually (hopefully) there are only a small number of exceptional cases. If so, you could hand edit the exceptions, for example: 2500 WEST SAM HOUSTON PARKWAY NORTH to 2500 WEST_SAM_HOUSTON PARKWAY_NORTH and then run your separation routine on the entire file

[SignExpress]

>>Usually (hopefully) there are only a small number of exceptional cases.<<

I'm working with over 3 1/2 million addresses...

[Villeroy]

>>Usually (hopefully) there are only a small number of exceptional cases.<<

I'm working with over 3 1/2 million addresses...

Using an arithmetic calculator with 1 million rows? Why?

[SignExpress]

one address per row (10 columns)

[Villeroy]

Did somebody enter the millions by keyboard into the spreadsheet? No?
So I guess you import those data from text files.
And the text files where exported from some type of database, possibly with numbers and names in separate fields.
And now you try to restore the database from text files without using a database.