Separate part of the same field

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Hi all,
I have a access table and one column contains data for our partners like
"Name Surname (Town/Country)". I would like to sort them about Town and
Country, so I have to spare data from one column to another two like:
- first column Name Surname
- second Town
- third Country

After all I will use Excel and Data >- Filter >- Autofilter to sort out.

My question: how to spare data from one access (or excel) line to another 2
columns.

Thnx a lot.
 
Create three alias columns in the query.
Name: Left$([partner],InStr(1,[partner]," "))
Surname: mid$([partner]instr(1,[partner]," ")+1,InStrRev([partner],"
")-InStr(1,[partner]," ")-1)
TownCountry: Right$([partner],Len([partner])-InStrRev([partner]," "))

This example assumes that every record has three values, that no values have
spaces within them, and that your column is called [partner].

If you want to do this in Excel, try Text To Columns under the Data menu.

Barry
 
Hi,
I'm not so familiar with Access and these queries, but Excel command Text To
Columns is PERFECT for me! I'll export access queries to excel and use this
command.

Thnx you very much!



Barry Gilbert said:
Create three alias columns in the query.
Name: Left$([partner],InStr(1,[partner]," "))
Surname: mid$([partner]instr(1,[partner]," ")+1,InStrRev([partner],"
")-InStr(1,[partner]," ")-1)
TownCountry: Right$([partner],Len([partner])-InStrRev([partner]," "))

This example assumes that every record has three values, that no values
have
spaces within them, and that your column is called [partner].

If you want to do this in Excel, try Text To Columns under the Data menu.

Barry


Dan said:
Hi all,
I have a access table and one column contains data for our partners like
"Name Surname (Town/Country)". I would like to sort them about Town and
Country, so I have to spare data from one column to another two like:
- first column Name Surname
- second Town
- third Country

After all I will use Excel and Data >- Filter >- Autofilter to sort out.

My question: how to spare data from one access (or excel) line to another
2
columns.

Thnx a lot.
Click to expand...
Click to expand...
 
Back
Top