Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help converting .txt(notepad) file to Excel (2000)
Hi,
Have data in a notepad/txt file in the following format: Joe Smith 123 Main St Anytown, AA 07340 Want this in Excel in the following format (5 columns 1 line). First Last Address City State Zip I've done this before, but don't recall the specifics with deliminated file and such and can't figure out so will let you pros guide me. Thanks -- Paul |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help converting .txt(notepad) file to Excel (2000)
My first recommendation is to look up the Excel Help topic
Split Text To Columns That'll show how to do it with the split text to columns method. But I think you're going to have a lot of work to do because of the arrangement of the data. Given some basic assumptions about your data, based on your example, we can do it with formulas. Basic Assumptions: The data groups are 3 rows long, without any blank rows in between them. Your names ONLY have a First and Last name (no middle initials) The city name is always followed by a comma and a space The State identifier is always 2 character abbreviation The Zip code is always a 5-digit code Begin by importing your text file into a single column (A) in a worksheet beginning at row 1. So the first name is in cell A1 of the worksheet. Set up labels in columns B through G for First Last Street City State Zip Remember that in the following formula examples, the editor here may split it up, but they are always one continuous formula. In cell B2 enter this formula [to get the first name]: =LEFT(OFFSET($A$1,(ROW()-2)*3,0),FIND(" ",OFFSET($A$1,(ROW()-2)*3,0))-1) In cell C2 enter this formula [to get the last name]: =RIGHT(OFFSET($A$1,(ROW()-2)*3,0),LEN(OFFSET($A$1,(ROW()-2)*3,0))-FIND(" ",OFFSET($A$1,(ROW()-2)*3,0))) In cell D2 enter this formula [to get the street]: =OFFSET($A$1,(ROW()-2)*3+1,0) In cell E2 enter this formula [to get the city]: =LEFT(OFFSET($A$1,(ROW()-2)*3+2,0),FIND(",",OFFSET($A$1,(ROW()-2)*3+2,0))-1) In cell F2 enter this formula [to get the 2 character state abbreviation]: =MID(OFFSET($A$1,(ROW()-2)*3+2,0),FIND(", ",OFFSET($A$1,(ROW()-2)*3+2,0))+2,2) In cell G2 enter this formula [to get the 5-digit zip code]: =RIGHT(OFFSET($A$1,(ROW()-2)*3+2,0),5) After that, you can 'fill' the formulas down the sheet as far as needed to pickup and convert all of the names/data in column A. See Excel Help topic on: fill formulas into adjacent cells At this point you have a few of options: #1 - you can leave things exactly as they are. #2 - you can hide column A just to clean up the appearance of the sheet. #3 - you can select ALL of the cells with the formulas in them (now showing all of the data split up properly) and use Edit | Copy followed immediately by using Edit | Paste Special with the [Values] option selected. That will replace the formulas with the contents and you can then delete column A entirely if you want to. But you might want to make a copy of the sheet before doing that if you plan on having to do this all over in the future - will save you having to type in all those formulas again. Hope this helps at least a little. "QuestionMan" wrote: Hi, Have data in a notepad/txt file in the following format: Joe Smith 123 Main St Anytown, AA 07340 Want this in Excel in the following format (5 columns 1 line). First Last Address City State Zip I've done this before, but don't recall the specifics with deliminated file and such and can't figure out so will let you pros guide me. Thanks -- Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting excel to notepad | Excel Discussion (Misc queries) | |||
Export notepad file to Excel | Excel Discussion (Misc queries) | |||
Filter Notepad file into Excel | Excel Worksheet Functions | |||
copy excell file to a CSV file and than to Notepad | Excel Discussion (Misc queries) | |||
Excel 2000 worksheets save incorrectly as notepad files. | Excel Discussion (Misc queries) |