Stripping Text

[Rozza]

Hi,

Say I've got a person's name in a single field which is in the following format:

Mr A B Cowlett

How do I seperate the "Mr" into a title field, the "A" into a Forename field, the "B" into a Middle Name field, and the "Cowlett into a Surname Field??

Any help would be greatly appreciated.

Cheers


Paul

 

[KenReay]

Hi

Depending on how uniform the names are...

With difficulty.

See help for the following Functions

instr()
Mid()
Right()
Left()

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[GDGarth]

Here's something you could try:

Open your table and use the replace function to replace each space with a comma.

Then your data will look like this:

Mr,A,B,Cowlett

Then export your table to a text file (.csv)

Next, Import your data to a new table, with separate fields as required. When you import, make sure you choose text file, comma delimited.

This assumes all names have a title, first name, middle name, last name, and that none of them have extra spaces in the names. If they do, you'll have to fix that.

 

[GDGarth]

Here's something you could try (requires absolutely no programming!):

Open your table and use the replace function to replace each space with a comma.

Then your data will look like this:

Mr,A,B,Cowlett

Then export your table to a text file (.csv)

Next, Import your data to a new table, with separate fields as required. When you import, make sure you choose text file, comma delimited.

This assumes all names have a title, first name, middle name, last name, and that none of them have extra spaces in the names. If they do, you'll have to fix that.

 

[Rozza]

Thanks!! I'll give it a try

 

[TomCologne]

Another option: Adjust the functions in

http://www.mvps.org/access/strings/str0001.htm

to your needs.

You're not alone,

TomCologne

 

[kaushal78]

write a module and create a temporary table
select the filed which u want to seperate, while in the data in ur delimeter should be comma(,) so while calling in module u seperate ur each filed with the delimeter (,) and insert ur record in other table..
Hope ur doubt would solve
kaushal