Sepearting Numeric Data from Letters

[jstock]

Hi,

I'm currently working on a school board campaign, and I purchased a
copy of public voting records for the last election of the campaign.
All the data was merged into just on column, and I've been able to sort
out everything except the addresses. Below is an example off a few rows
off the spreadsheet.

'[image: http://www.imgshost.com/uploads/219d5889dc.jpg]'
(http://www.imgshost.com)

The main problem I'm having is in street names that a number in them,
such as S 14 Street. Any formula I write to seperate numbers pulls the
14 out of the street name, and not just the numbers to the mailing
address. I was wondering if there was a formula out there that could
seperate it, and have a new column with just the street number, such as
"3389", and a different column to have the street name, such as "S
6th".

I would appreciate any help on being able to sort these voting records
out so I can prepare some mailings. Alternatively, if I could send
someone the spreadsheet I have and they could write the formula in
themselves, that would help a great deal.

Thank you,

Jeremy Stock

 

[Biff]

Hi!

Try this:

Enter this formula in B1:

=TRIM(SUBSTITUTE(A1,C1,""))

Enter this formula in C1. This is an array formula. It MUST be entered using
the key combination of CTRL,SHIFT,ENTER:

=TRIM(MID(A1,MATCH(0,(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>=48)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<=57),0),255))

Select both B1 and C1 and copy down as needed.

Note: There is not a single "bulletproof" formula for extracting addresses
because there are just too many possible variables to contend with. This one
will work based on your sample data.

Biff