Extract and paste a number from one field to another

[Mike_FM]

Hi all, I'm new to this forum and to filemaker and looking for some help.

I have the following number in a field 101_010_0010 and I want to extract 010 from the middle of the number and paste it to another field. I have been trying to do it with replace field contents but with no luck... does anyone have any advice on how I can achieve this?

Many thanks,

Michael

[rwoods]

Hi Mike

What you have is some text, rather than a number, to begin with. Hopefully that string '101_010_0010' is in a Text field, not a number field!

I'm not sure if you really mean 'paste' (as in 'copy and paste'), or whether you are trying to extract it using a calculation or a script?

The other question I would need to ask is :-

Is the format of the string always the same? i.e Is it always the information between the first and second 'underscore' that is required. 

If so, the the following FileMaker calculation will find that for you (there are many other ways of achieving this)

GetValue ( Substitute ( yourStringField ; "_" ; "¶" ) ; 2 )

This works by converting your string to a carriage-return-separated list of the separate sections (the parts between underscores), and then taking the second one from the list. This means it will work regardless of how long each section is.

It converts it to :-
 

101

010 <- and then selects this one

0010

You could use this calculation in another field definition or in a 'Set Field' line in a script.

Let me know if you needed something different.

[Mike_FM]

Hi there, thanks for your reply

Apologies, yes I'm trying to extract using a calculation like the one you have written for me.  I'm not so sure how I fill in the expression though... is it like

GetValue ( Substitute ( yourStringField ; "101_010_0010" ; "010" ) ; 2

Also the the string field I'm extracting from was in a number field and have now changed to text field... I never thought of it as text as the characters are numerics so when would I use a number field in FM?

Thanks so much!

[rwoods]

Hi Mike

You need to leave my calculation mostly as it is, and just replace the 'yourStringField' part with the name of the field in which you have the data ("101_010_0010"). Something like this :-

GetValue ( Substitute ( table1::field1 ; "_" ; "¶" ) ; 2 )

I'm not sure how else to describe this! 

A number field in FileMaker would just hold a numbers, like 12 or 10.9

You cannot store any other characters, except 0-9 and your decimal point character.

Because your text has underscores then it cannot be a number, it is a string of characters and therefore belongs in a text field.

[Mike_FM]

Thanks so much, that works very well!