Extract data from field

[gnfb]

I import some data from a online source. Part of that data that goes into one field is separated by commas like>

graham jones, 123 high street,town,

or

mr graham jones,123 high street,town,

i need to be able to separate the text before the first comma to a different field.

I cant use leftwords as the text before the first comma varies

thanks for any input

[comment]

Try:

Left ( text , Position ( text , "," , 1 , 1 ) - 1 )

[gnfb]

Thank you for that works brilliantly!!!

[gnfb]

So whilst you are on a roll how then do i get the address data only?

if

john smith,123 high street,the place,downunder

Left( details , Position( details, "," , 1, 1) -1)

gets

john smith

what gets

123 high street,the place,downunder

[comment]

Hint: we want x characters, this time counting from the right, up to the first comma in the text. Subtract the position of the first comma from the total length of text to get x.

[gnfb]

so are you saying it would look like

Right( details , Position( details,, 1, 1) -1)

sorry I have a little knowledge and are dangerous with it, appreciate your help I tried a few combinations and got know where

[comment]

No. Please read more carefully. I said you need to find the total length of text (using the Length() function). Then subtract the position of the first comma from the total length - that is the number of characters you need to use with the Right() function.

[gnfb]

Sorry im not a programmer so my reading of logic is limited

The only trouble with your solution is that the text length varies ie

graham,123 high street,town,london

graham foster,12345 high street,the town new zealand

the field that i want to extract the data from are name and address fields

[comment]

Sorry im not a programmer

Neither am I. The question is: do you want to learn how to do this, or do you want someone else to do it for you?

The only trouble with your solution is that the text length varies

The text length varies. And the position of the first comma varies, too. And so does the result:

text = "graham,123 high street,town,london"

Length ( text ) = 34

Position ( text , "," , 1 , 1 ) = 7

34 - 7 = 27

Right ( text , 27 ) = "123 high street,town,london"

and:

text = "graham foster,12345 high street,the town new zealand"

Length ( text ) = 52

Position ( text , "," , 1 , 1 ) = 14

52 - 14 = 38

Right ( text , 38 ) = "12345 high street,the town new zealand"

[gnfb]

Ok cool I actually got it working but the way i have it working is by having fields for the individual functions. In the vane of me learning rather than someone just giving me the answer, is this necessary or could i put it all in a script you don't need to tell me how to do it just where i should be looking ie can it all go in a script or does it have to be in separate fields to achieve the result,or is there another way to get it to process the information. just seems a bit wasteful having all those fields

[gnfb]

scratch last comment got it sorted and the formula im using is

Right( details, (Length(details))-(Position( details, "," , 1 , 1 )))

[comment]

having fields for the individual functions

I used to do the same thing - it's convenient both for learning and for troubleshooting. Once you have it working, you can simply copy a formula and paste it into the next one, replacing the reference.

If you upgrade to version 7 or above, you will find the Let() function can also be used to break a calculation into digestible chunks.

[gnfb]

yes breaking it down then building it up helps me I actually have filemaker pro 8 but as its not a straight upgrade to transfer my databases from 5 and because i use the databases everyday for business I am not game to change The old motto if it aint broke dont fix it as i get more time i may look at changeing it all over

But hey thank you for your guidance on this