Hello,

I've got a worksheet with address data in it. I'd like to put house
#'s and street numbers in seperate columns.

For example;
"2304 41st Ave E" would become "2304" "41st Ave E"

Can anybody help me figure out how to do this?

Hi seaone,

Ok, what you want to do is not difficult, but it would be a whole lot
easier using ASAP Utility. If you don't have it, you can download it
here (you'll find it useful for other things too and soon won't know
how you got along without it):

"ASAP Utilities contains over 300 useful and powerful utilities to
fill the gaps in Excel, and automate frequently used tasks.
Guaranteed to save you many hours of time !"
http://www.asap-utilities.com/

STEP ONE (separate the address into four columns)

1) Select the column you want to change (let's say A).
2) Click on DATA / TEXT TO COLUMNS
3) Select DELIMITED / Next
4) Select TAB and SPACE / Next
5) Click on the DESTINATION LITTLE BOX (to the right of the field)
6) Select as many columns as you need for the addresses (say A - D).
7) Click on the DESTINATION LITTLE BOX again / Finish

STEP TWO (merge three columns into one)

1) Select columns B - D 
1) Click on your ASAP Utility (top bar)
2) Columns / Rows
3) Merge column data (join cells)
4) Press the "Space" bar / OK

You should now have 
  A         B
"2304" "41st Ave E"

If you have any questions, please let me know and we'll go over it. I
walked myself through this using my excel and it worked fine - I hope
it will be fairly easy for you.

Thank you,
hummer

---

Request for Answer Clarification by seaone-ga on 15 Jul 2004 07:38 PDT

thank you hummer. Looks like an interesting tool. I'll definitely
spend some time with it. I ended up figuring it out using standard
excel functions.

here's what I came up with to split the cell;
* for house number extract to column (relative);
=LEFT(I2,4)
this worked because all house numbers were four digits.

* for street name extract to column (relative);
=MID(I2,6,20)

---

Clarification of Answer by hummer-ga on 15 Jul 2004 08:28 PDT

Hi seaone, thank you for your clarification - good job! Well, now you
have two methods to choose from (mine may look like alot of steps, but
it really isn't that bad). One advantage to my way is that it's not
fussy about the number of digits - it just uses the spaces between
words.

I neglected to include this in my answer:

After Step One, you should have:
  A       B     C    D
"2304   41st   Ave   E"

Then it is just a matter of combining BCD into one column B (Step two using ASAP).

At any rate, the important thing is you're in good shape and working well.

Sincerely,
hummer

The Text to columns function is a built-in Excel function. It is on the Data menu.

forge

Hi forge, thank you for your comment. Yes, that is what I used in Step
One. Thanks, hummer

it's all well and good while you have 4 digits of numbers. You could
be more generic:
=LEFT(A1,SEARCH(" ",A1)-1)
=MID(A1,SEARCH(" ",A1)+1, LEN(A1)-SEARCH(" ",A1))

PS I use the above to separate Last,First all the time as well (change
space to comma)