I have a field that has for example this:

2 Regents Wharf,

All Saints Street,

London

N1 9RL

Tel: 020-7837-6433

Fax: 020-7520-8100

Email: [email protected]

Web: http://www.gratte.com

I want to seperate out the tel, fax, email and web to another field, my trouble is that they are not always on the same lines, I know that I need to use the postion command and "Tel:" but cannot quite get it right. Any help would be most welcomed.

Thanks.

Does the address have Labels for it (i.e. Name:, Street:, Cith:, Zip:), like the phones and email do?

Lee

The address does not have any labels and can be anywhere between 2 and 6 lines long but pulling out the addresses manually will not be too much of a problem if the tel fax email and web have been seperated automatically.

Thanks Lee, sorry for the delay in posting but I am in the UK.

I'll show you how to do this with the fax number, it's easy to change it over then for your other fields.

Fax=

Middle(text ,Position(text , "Fax", 1, 1), Position(text ,"EMail", 1, 1)-Position(text ,"Fax", 1, 1)-1)

--->Where it says text, replace with the name of your address field

I'll break it down for you so you can see what is happening.

Middle(text --->We're after characters in the middle of the "text" field

,Position(text, "Fax", 1, 1), --->We're telling it where to start getting the characters from - the start position in the "text" field

Position(text ,"EMail", 1, 1)-Position(text ,"Fax", 1, 1)-1) --->We're telling it how many characters to get. But how do you know where the fax number ends - this will tell you. It takes the position of "Email" and minuses the position of "Fax", this will give you the number of characters in the fax number - but it also gives you the chracter return at the end of the fax number so that is what the -1 is for at the end of the calculation.

Hope this helps

Ed.

Hi again,

Just realised that the result of the calculation would be:

Fax: 020-7520-8100

I'm guessing you will not want this, you just require the number - right?

If so change the calculation to this:

Fax=

Middle(text ,Position(text , "Fax", 1, 1)+5, Position(text ,"EMail", 1, 1)-Position(text ,"Fax", 1, 1)-6)

The changes I have made are the inclusion of the +5 and I have amended the -1 at the end to -6

The +5 will take the start of Fax and add 5 characters on before it starts extracting characters, therefore ridding the "Fax: "

Changing the minus 1 to minus 5 was because not only do we need to get rid of the character return, we now need to get rid of the extra 5 charcaters we added on to the satrt (they will now appear at the end unless we minus 6)

I don't think I've done a great job of explaining that - but I hope you understand, if not let me know and I'll try and put it into English instead of gobble-de-gooke!

Ed.

Another note!

When it comes to extracting the Web address, you do not have a next field to compare it with to get the length, therefore you need to use the total number of charcaters in the field for comparison as so...

In fact I'll just show the calculation for each, it'll be easier!

Tel=

Middle(text ,Position(text ,"Tel", 1, 1)+5, Position(text ,"Fax", 1, 1)-Position(text ,"Tel", 1, 1)-6)

Fax=

Middle(text ,Position(text ,"Fax", 1, 1)+5, Position(text ,"EMail", 1, 1)-Position(text ,"Fax", 1, 1)-6)

EMail=

Middle(text ,Position(text ,"EMail", 1, 1)+7, Position(text ,"Web", 1, 1)-Position(text ,"EMail", 1, 1)-8)

Web=

Middle(text ,Position(text ,"Web", 1, 1)+5, Length(text)-Position(text ,"Web", 1, 1))

Ed.