Breaking apart info in fields

  • Thread starter Thread starter Sara
  • Start date Start date
S

Sara

I am working in Access 97. I know how to concatenate
fields in Access & Excel. However, I've just received a
database that has a full name field & I'm wondering if
there is a quick, easy way to break the full name field
apart into separate fields - a reverse of concatenation,
of sorts. Any suggestions? I'm willing to work on
cleaning this field up in either access or excel.
 
This is a pain but sometimes you have to do it.

Get the names into Excel.
Save the file as an TXT.
Open the TXT file and Excel should go into Import Wizard.
Select Delimited and for character enter a SPACE.
Now you should see them line up pretty nicely but if some names have
initials or more than 2 words, you will have to fix those manually. If they
have more than 2 words, then you can sort those together and delete the
middle column.

Hope that helps.
 
Sara said:
I am working in Access 97. I know how to concatenate
fields in Access & Excel. However, I've just received a
database that has a full name field & I'm wondering if
there is a quick, easy way to break the full name field
apart into separate fields - a reverse of concatenation,
of sorts. Any suggestions? I'm willing to work on
cleaning this field up in either access or excel.
Click to expand...

(Similar to what "access" said, but...)

1. Open the Access database, and open Excel. Tile both windows horizontally.
2. Click and drag the name of the Access table you want to parse to a blank
Excel worksheet, which copies the data into the worksheet.
3. Insert a new column to the right of the column you want to parse, and
give the column a field name.
4. Select the cells you want to parse.
5. Go to Data | Text To Columns to start the Convert Text To Columns Wizard.
6. Select Delimited, and click Next.
7. Select the delimiter used to separate the data you want to parse. (For
example, if the data consists of a last name followed by a first name
separated by a space, choose Space as the delimiter.)
8. Click Next, and click Finish.
You can now import the parsed data back into the Access database
 
Back
Top