Separate Street Addresses - Text to Columns

  • Thread starter Thread starter RERyan
  • Start date Start date
R

RERyan

hi there - I have a list of street addresses that I need to get int
two separate colums. Currently the street numbers are "stuck" to th
street names on all streets that have more than one number.

For instance all entries similar to "10 Main St" are OK, but not "10-1
Main St". They are inputted like this "10-11Main St". How can
separate these with a formula?

Here are the first several entries. Thanks for any help!

987 --989Salem St
5 --7Coleman St
97 --99Madison St
11 Wellington St
9 Woodland St
18 Arcadia St
24 Blaine St
53 --55Sammett St
473 Salem St
21 Medford St
153 Mount Vernon St
39 Garden St
25 --27Winthrop St
8 --10Knollin S
 
Hi,

Assuming that the entries begin in A1, and you want the numbers in column B,
with the street names in column C, type this into B1:

=LEFT($A1,MAX(ROW(INDIRECT("1:"&LEN($A1)))*(MID($A1,ROW(INDIRECT("1:"&LEN($A
1))),1)>="0")*(MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1)<="9")))

as an array formula (hold Ctrl+Shift while pressing Enter).

Then this into C1:

=TRIM(RIGHT($A1,LEN($A1)-LEN(B1)))

(which isn't an array formula).

Copy B1 and C1 down as far as required.


The first formula extracts the numbers by finding the position of the last
numeric character in the cell. Then the second simply gets whatever
remains, and trims the spaces (that would be left in the standard entries).
I'm fairly sure that there will be a simpler formula for the first one, but
I've been out of the Excel puzzle solving scene for quite a while now, so
the regulars may have to forgive me for not keeping up (and not remembering
stuff that I haven't used in quite a while).

Steve D.

P.S.
I've got a funny feeling that there may be a kickself coming when the
regulars do spot this one...
 
Back
Top