Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sepearting Numeric Data from Letters
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 -- jstock ------------------------------------------------------------------------ jstock's Profile: http://www.excelforum.com/member.php...o&userid=33807 View this thread: http://www.excelforum.com/showthread...hreadid=535781 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sepearting Numeric Data from Letters
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 "jstock" wrote in message ... 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 -- jstock ------------------------------------------------------------------------ jstock's Profile: http://www.excelforum.com/member.php...o&userid=33807 View this thread: http://www.excelforum.com/showthread...hreadid=535781 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a new line in spreadsheet | Excel Discussion (Misc queries) | |||
Return Single Row of Numeric Data to Single Column | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Inserting Filtered RC cell information into other worksheets | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel |