Jump to content

More seperating!


This topic is 6561 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6561 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.