software

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: www.imgshost.com/uploads/219d5889dc.jpg]'
(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
quot;3389quot;, and a different column to have the street name, such as quot;S
6thquot;.

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--
jstock
------------------------------------------------------------------------
jstock's Profile: www.excelforum.com/member.php...oamp;userid=33807
View this thread: www.excelforum.com/showthread...hreadid=535781Hi!

Try this:

Enter this formula in B1:

=TRIM(SUBSTITUTE(A1,C1,quot;quot;))

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(quot;1: quot;amp;LEN(A1))),1))gt;=48)*(CODE(MID(A1,ROW(INDIRECT(quot;1: quot;amp;LEN(A1))),1))lt;=57),0),255))

Select both B1 and C1 and copy down as needed.

Note: There is not a single quot;bulletproofquot; 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

quot;jstockquot; gt; wrote in
message ...
gt;
gt; Hi,
gt;
gt; I'm currently working on a school board campaign, and I purchased a
gt; copy of public voting records for the last election of the campaign.
gt; All the data was merged into just on column, and I've been able to sort
gt; out everything except the addresses. Below is an example off a few rows
gt; off the spreadsheet.
gt;
gt; '[image: www.imgshost.com/uploads/219d5889dc.jpg]'
gt; (www.imgshost.com)
gt;
gt; The main problem I'm having is in street names that a number in them,
gt; such as S 14 Street. Any formula I write to seperate numbers pulls the
gt; 14 out of the street name, and not just the numbers to the mailing
gt; address. I was wondering if there was a formula out there that could
gt; seperate it, and have a new column with just the street number, such as
gt; quot;3389quot;, and a different column to have the street name, such as quot;S
gt; 6thquot;.
gt;
gt; I would appreciate any help on being able to sort these voting records
gt; out so I can prepare some mailings. Alternatively, if I could send
gt; someone the spreadsheet I have and they could write the formula in
gt; themselves, that would help a great deal.
gt;
gt; Thank you,
gt;
gt; Jeremy Stock
gt;
gt;
gt; --
gt; jstock
gt; ------------------------------------------------------------------------
gt; jstock's Profile:
gt; www.excelforum.com/member.php...oamp;userid=33807
gt; View this thread: www.excelforum.com/showthread...hreadid=535781
gt;