Messy Text to Columns

[sweeneysmsm]

I am trying to fix up a poorly designed Access database.

One of the tables has a Next of Kin field in which the typical entry looks
like this once it has been exported to Excel:

Mary Smith



Next of Kin:

Notify:



Mr. & Mrs. John M. Smith

(Parents)

(live with Son,Gerard)



Mr.Gerard H. Smith (brother)

1234 Brookside BLVD.

Kansas City, MO 12345

Tel: 1-816-123-4567

Mrs. Regina Thomas (sister)

123 Beech St.

Worcester, PA 12345

Tel. 1-215-123-4567

Within the field there are paragraph entries.

I exported it to Excel in hopes of deleting some of the extraneous material
and then separating the data into columns using Text to Columns.

In the cell the paragraph marks show up as bangs; in the formula field at
the top the bangs show up as tiny squares.

When I try to to do a Find and Replace, I can delete text, but I am still
left with the bangs. I also can’t deal with the Text to Columns because of
the bangs.

My hope had been to setup the fields correctly in Excel and then import them
to a new Access table set up properly. Is there any way of doing this or is
the best thing to simply keep the Excel table open and type the data into an
Access Form? There are many records

Thank you for any help given.

Mary

 

[Dave O]

If I can voice an opinion, you may find yourself better off performing
all the clean-up and conversions within Access. It sounds like you're
already having to develop workarounds to get the data into Excel, and
those workarounds may be Excel hurdles that you wouldn't have to deal
with if you did the work in Access.

By working with "Make Table" queries, you could parse the data directly
into new tables without altering the original data and without concern
for the volume of records (since "many records" is a breeze for
Access). Queries are amazingly robust, and you should be able to parse
fields quickly and easily.

 

[sweeneysmsm]

Hi, Dave.

Thanks for your reply.

Perhaps I am not understanding you. I am somewhat familiar with make table
queries and use them frequently. I don't see how it would help me, but if you
can explain that would be great.

If I were to use a Make Table Query, I would choose the one field in the
original table that has all of the information above. I am not clear on how I
would parse that to get the data split into separate fields.

The ability to split the data in one column into several was why I looked to
Excel. I am not aware of how I could do this in Access.

I would really be interested in your reply.

Thanks so much.

Mary

 

[Dave O]

Hi- apologies for the delayed response.

In the Make Table query, you can parse a field using the MID function
in the parameter row of the query, in this format:
Mid([field name],1,10)
where 1 is the column to start in, and 10 is the number of columns to
return.