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.
