Hi everyone

I hope I am posting this in the correct place - apologies if not.

I have a couple of fields in my database, call them field A and field B.

Field A contains a file name which may or may not start with letters but always contains an underscore and then some numbers. I need to copy the numbers after the underscore to field B.

How should I best go about doing this.

Many thanks

Milt

Make FieldB a calculation field =

Right ( FieldA ; Length ( FieldA ) - Position ( FieldA ; "_" ; 1 ; 1 ) )

Hi Milt, and welcome to the Forum.

As long as the data in FieldA is exactly as you stated, then the calculation provided by comment will do what you want. You can also do this using the Filter Function i.e. Filter ( FieldA ; "0123456789" )

Trouble is, sometimes the data isn't as consistent as we think it is. An example of this might be alpha characters after the separator, or a numeric character before the separator, and needing to deal with it too.

examples of what I mean.

ABCED_12345 what you described

ABCED_12345A an Alpha character after the separator?

ABCED7_12345 a numerical value before the separator?

ABCED7_12345Aor both?

The example file attached shows these examples and what happens using the two methods, and combining them.

HTH

Lee

FilterFunction.fp7.zip

Another method:

Let ( x = substitute( fieldA; "_"; "¶");

GetValue( x; 2)

)