Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Address fields in columns...
Hi - one day i'll be able to help but alas my excel quest leads me to learn rather than coach until that time comes, i need your help.
i have 10,000 names and address in a .txt form which i need to import into excel, save as a .csv. The names are formatted as below: Name : fred blogs address: 1 the street addres 2: address 3: Derby plus the usual fac line etc... (10000 times in column a1:b1) how am i able to have the data under one heading and 10000 rows....? your help would make me very happy!! nick |
#2
|
|||
|
|||
Quote:
Can we have some before and after dummy data in Excel format? Makes it far easier to help. S. |
#3
|
|||
|
|||
Quote:
For su Address details from the *.txt file are formated as follows Name: Fred Blogs Address1: The Street Town: New Town County: Sussex PC: AB1 2CD Telephone: 01234 567890 Fax: 01234 567899 Name: Jane Blogs Address1: The Old Street Town: Old Town County: Sussex PC: DC2 1BA Telephone: 0987 654321 Fax: 0987 654322 10,000 more names... All names are formated in this way. When I import the text using the wizard it sorts the heading from the text no probs. (I add the : into the criteria and it inserts the column. WHat I would like to achieve is he follow Name Address1 Town County Post Code Tele FAX Fred Blogs The Street New Town Sussex AB1 2CD Jan Blogs The Old Street Old Town Sussex DC2 1BA hope this is enough to help |
#4
|
|||
|
|||
Quote:
Is there a blank row (or rows) between ach person? Is the layout consistent throughout? i.e. 7 rows do data and then a blank / blanks? Or could this vary through the data? |
#5
|
|||
|
|||
Quote:
This is the format (exactly) as it is in the TXT file. --- Name: Fred Blogs Date Of Birth: 01/07/1999 Address: 1 New Street Newtown Sussex AB1 2CD Phone: 0123 456789 --- Name: Janet Blogs Date of Birth 01/06/1980 Address: 1 Old Street Newtown Sussex DC2 1BA Phone: 0987 654321 --- I know the second address line has no heading and this is going to cause me problems! |
#6
|
|||
|
|||
Quote:
But, before I can provide a solution, I'll be needing some sample data in Excel format. You can add an XLS or XLSX file to a compressed file (or use WinZip if you have it) and then attach that here. I really cannot do much at all with the text examples you've posted above. S. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Address fields in columns...
I know the second address line has no heading and this is going to cause
me problems! I think not, but anyhow there's a more ominous question. Wikipedia shows that county names in the UK can be from one word to five words. Presumably, town names can also be one or more words. So the question is how to mechanically split a cell to separate those two names. Is there something in the raw data that would help? Otherwise, there could be some kind of matching with county names in a fixed list. Alternatively, would it be acceptable for the result to have town and county combined in a single column? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Address fields in columns...
This is the format (exactly) as it is in the TXT file.
--- Name: Fred Blogs Date Of Birth: 01/07/1999 Address: 1 New Street Newtown Sussex AB1 2CD Phone: 0123 456789 --- Name: Janet Blogs Date of Birth 01/06/1980 Address: 1 Old Street Newtown Sussex DC2 1BA Phone: 0987 654321 --- Here's one way to approach the problem. It relies on the regularity of the source data, by counting rows and characters within rows. It uses a fixed list of county names to parse the second address line. Sheet1 column A holds the source data. It starts at A1 with the first "---" that begins the first data record. Sheet3 will hold the result array. Sheet2 is for intermediate results. Its rows parallel Sheet1 (with two extra rows on top). Once sheets 2 an 3 are set up, they are not edited when new source data is pasted into sheet 1. Let's start with Sheet2, and then do Sheet3. ------ Sheet2 ------ In A3, put =TRIM(Sheet1!B2) and copy down as far as needed. This keeps only the part of the source data that contributes to the result. In B3 put =LEN(A3) and copy down as far as column A can go. This is the length of each "column A" value. In C3, put =IF(MOD(ROW()+1,8)=0, IF(MID(A3,B3-6,1)=" ",6, IF(MID(A3,B3-7,1)=" ",7,8)), "") and copy down as far as column A can go. This is the length of each post code. Starting in E1 an going to the right, enter the county names. If there are alternate spellings, include each spelling separately. Put this in E2 =LEN(E1) and copy rightward as far as needed. It's the length of each county name. Put this in E3: =IF($C3="","",IF(E$1=MID($A3,$B3-$C3-E$2,E$2),E$2,"")) and copy rightward and downward as far as needed to fill the rectangular array. This compares: - the "column A" text directly before the post code with - the county names. Where they match, it displays the county name's length. There might be more than one match on the same row. In D3 put =MAX(E3:CD3) and copy down as far as column A can go. (If county names go beyond column CD, extend the range.) This chooses the longest county name matched for each record and displays that county name's length. ------ Sheet3 ------ Sheet 3 draws data from only Sheet2!A:D. In A2 put =INDEX(Sheet2!$A:$A,8*(ROW()-1)-4) In B2 put =INDEX(Sheet2!$A:$A,8*(ROW()-1)-2) In E2 put =RIGHT(INDEX(Sheet2!$A:$A,8*(ROW()-1)-1), INDEX(Sheet2!$C:$C,8*(ROW()-1)-1)) In C2 put =LEFT(INDEX(Sheet2!$A:$A,8*(ROW()-1)-1), LEN(INDEX(Sheet2!$A:$A,8*(ROW()-1)-1)) -1-LEN(E2)-INDEX(Sheet2!$D:$D,8*(ROW()-1)-1)) In D2 put =MID(INDEX(Sheet2!$A:$A,8*(ROW()-1)-1), LEN(C2)+1, LEN(INDEX(Sheet2!$A:$A,8*(ROW()-1)-1))-LEN(C2)-LEN(E2)) Select A2:G2 and copy down as far as needed. Enter the column headers in row 1. Hope this helps getting started. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How does an address appear to have 2 fields in 1 - see sample data | New Users to Excel | |||
how can I convert data with fields in rows to fields as columns | Excel Discussion (Misc queries) | |||
Address is in separate fields | Excel Worksheet Functions | |||
How do I take some of my Excel fields and make address labels? | Excel Discussion (Misc queries) | |||
Can't get Outlook to recognize address card fields when importing. | Excel Discussion (Misc queries) |