importin address info and separating

  • Thread starter Thread starter SKE99
  • Start date Start date
S

SKE99

The way I did this was pretty crude and I only do this kind of thin
every year or two. The situation is we need to import addresses from
text file and then separate the addresses into the various components
The text file consists of data in the format "Anderson, William H,"91
Prospect St","Fort Morgan, CO 80701-3041","970-867-6992"
The way I originally set it up is two sheets in a workbook. One is Dat
where the data is imported to at A1. The field headers when importin
are Household_Member1, Address, City,_State_Zip, Area_Code_and_Phone s
the actual data starts at DATA!A2.

On the Revised_Data sheet I originally determined comma position fo
the comma in the name =IF(Data!A2="","",RIGHT(Data!A2,J8)) put it i
column 8 then used that to separate Firstname and initial, Lastname
The rest of the fields we left as is

Now we need to redo it into the following fields
Firstname
Initial
Last name
address
City
State
ZIP
ZIP+4 (just the 4 digits)
AREA CODE
PHONE

Any Recommendations on how to simplify this.
at times there are thousands of names being imported to excel that i
then used for a mailing list.

TIA for your hel
 
When you open a text file in Excel you can text to column
the file and separate the information into separate cells.
using the , as the separator.
use delimited and just follow the instructions.
It's under Data - text to columns

Then you can use this to make mailing labels in word
 
That doesn't really address the issue as the firstname+Initial does not
have a delimiter and is of variable width (possibly use the space
between them in and use LEFT function and RIGHT function).
Separating the City,_State_Zip can partially be done by keying off of
the comma but that gets me back to the approach I used earlier but
there should be a more elegant approach.
Separating the area code and phone is simple because of the fixed
length

This will be used by a clerk who knows just enough to import at this
point and can't be expected to do much more, so it should be automated
as much as possible.
 
I've continued with my crude approach and it is as follows

FIRSTNAME =IF(Data!A2="","",RIGHT(Data!A2,N8)) (This Brings in
Firstname and initial. Any Suggestions on how to handle if no middle
initial. need to search for space and key off of that)

Initial =IF(Data!A2="","",RIGHT(Data!A2,1)) This works except
where there is no middle initial

Firstname =IF(Data!A2="","",LEFT(Data!A2,L8))

ADDRESS =IF(Data!$A2="","",+Data!B2)

City =IF(Data!C2="","",LEFT(Data!C2,O8-1))
State =IF(Data!C2="","",MID(Data!C2,O8+2,2))
Zip =IF(Data!C2="","",MID(Data!C2,O8+6,5))
ZIP+4 =IF(Data!C2="","",RIGHT(Data!C2,4))

K thru O are numbers that denote comma position in name field,
characters left of comma, Length of text string, start of first name,
and comma in city_state field

all works, just trying to figure out how to handle those without a
Middle initial and bringing just the first name in (will look at Mid
function some more but again the lack of a middle initial muddies the
water)
 
Back
Top