Moving select information from one column to another

[Guest]

I have a file that is about 7,000 entries long and I need to separate a
column with city & state so I can put the info into our main database (it has
city and state separated). Is there a formatting formula I can use either in
Excel or Access? Also, is there any way to do the same thing for first and
last name?

 

[Rick B]

This is very easy in Excel using the TEXT TO COLUMNS feature.

It can also be done in Access with an update query.

 

[Michael J. Strickland]

1. Import the file into a table. Name the City-State field CS.
2. Create two new fields in your table to the right of the CS field call
"City" & "State".

3. Make a query on the table and insert columns to the right of the CS
field.

4. In the criteria for the first column, put:
Left([CS],InStr([CS],",")-1)

5. In the criteria for the first column, put:
Trim(Right([CS],Len([CS])-InStr([CS],",")))

Note: the above two expressions assume a comma separates the city and state.
If they are separated only by a space, use the InstrRev function instead of
the Instr function and replace "," with " ".

6. Close and Save the query, then open it.

7. Copy these two columns (expressions) into the City and State columns
(fields) you initially added to the table. To do this, click on the
expression column header, hit Ctl-C, click on the City column header, hit
Ctl-V).

Now your table should have the city values in the City field and the state
values in the State field.

 

[John Vinson]

I have a file that is about 7,000 entries long and I need to separate a
column with city & state so I can put the info into our main database (it has
city and state separated). Is there a formatting formula I can use either in
Excel or Access? Also, is there any way to do the same thing for first and
last name?

It's possible but might be a bit tricky using an update query. It
depends on how your data is formatted - you need some way to isolate
the state from the city-state field. Does the state always appear
last? Always as the two-letter state code or sometimes as the state
name? Is it preceded by a comma? if so, sometimes or consistantly?

Names are similarly tricky: Lisa May Grovner's first name is Lisa May;
Hans de la Meer's first name is Hans. It can be tricky deciding what's
first and what's last!

A ROUGH CUT approach would be to add City and State fields to your
table, and run an Update query updating City to

Trim(Left([CityState], InStr([CityState], " ") - 1))

and State to

Trim(Mid([CityState], InStr([CityState], " ") + 1))

replacing " " by "," if you consistantly have a comma before the state
name. This will give you a City value of "Salt" and a State of "Lake
City UT" as written - you'll need to allow enough room in the State
field for this, and do a followup query for states containing a blank.

John W. Vinson[MVP]