Split a street address field apart.

[WGD]

How do I split a street address field apart? Field will have 2-3-4-5
digits, followed by a space then a series of alphas. i.e. 837 Third
Avenue -or- 6 Street West.

In a column of these address fields, wish to create two fields: the 2-5
numericals only in one, and the remaining alphas in another.

Not VBA-oriented, please, what might be the best, and simplest for my mind,
way to achieve this?

Using Office 2007.

Kind Regards,
Wayne Dengel

 

[Ron Coderre]

Try this:
A1: (address text....eg 837 Third Avenue)

The number
B1: =LEFT(A1,FIND(" ",A1)-1)

The street
C1: =TRIM(MID(A1,FIND(" ",A1)+1,255))

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[JP]

Does this help?

http://www.uwec.edu/Help/Excel07/ws-separate.htm


HTH,
JP

 

[WGD]

Will give is a sincere try. . . and Thank You!
Wayne

 

[WGD]

Another good solution. Appreciated.

I did this kind of thing a few, actually many years ago - gray matter could
not pull from deep within the solution I used back then.

Does this help?

http://www.uwec.edu/Help/Excel07/ws-separate.htm


HTH,
JP

 

[WGD]

Worked perfectly - Thank You.
What annoys me is that at one time I could think this through "all by
myself". Times have changed!
Kind Regards,
Wayne

The post that followed your, will work through that one also - maybe jog the
brain into thinking again!

 

[WGD]

Ron:

I use the two equations noted below, those that you suggested. They work
very well. However,
Here is a problem: after splitting out number(s) from the street name, I
then sort the whole table, ranking by street number. What happens is the
following: lines (rows) sorted by the street number field end up as follows:

1245
2456
345
4567
6789
750

where the line with 345 (which is numerically lower than 1245) should
preceed the line with 1245; likewise for 750. Preferred ranking would be:

345
750
1245
2456
4567
6789

What do I need to do to effect this change?

Thank You!

Wayne Dengel

 

[Gord Dibben]

The numbers are Text due to the splitting out with a formula.

Format all to General, copy an empty cell.

Select the range to change then Paste Special>Add>OK>Esc.

Now try the sort.


Gord Dibben MS Excel MVP

 

[WGD]

You folks are the greatest! An items as old as this one is - your quick
response is greatly appreciated.

Wayne

 

[WGD]

Gord: Not that you did not provide a working solution, I may have not
properly understood the "how to".

Looking at the original equations below, they start out formatted as
General. The splits are also General. Nolthing was/is in Text form.

So now I have columns of three cells in a row: Nbr only ... Str Name only
..... Orig Nbr & Name

I looked at the formats of all three columns: all are General. What did I
not understand, not how you said it, but, with limited experience, how I
executed what you said!

Pls bear with me again! Tks!

Wayne

 

[Gord Dibben]

First of all........have you converted the formulas to values?

Select the columns and Copy then Paste Special(in place)>Values>OK>Esc

The preceding step will leave the numbers as text.

Next steps to convert back to numbers.

Copy an empty cell.

Select the column with the numbers.

Edit>Paste Special(in place)>Add>OK>Esc

The numbers should now sort correctly


Gord

 

[WGD]

Gord ~ Will work the solution at work on Monday. Thank You!
Wayne