Extract 7 Digits between [ ]

[Zippy Aus]

Hi all

I am looking for a calc or custom function that will reliable extract the seven digit code in a text field that is between two square brackets.

eg. field value is My letter to Smith [1234567], I want to be able to extract 1234567

The issue I have is when there is another[ in the field eg. [FWD] My letter to Smith [1234567]

The code I have used so far is

Let ( [ 



$StartItem = 

Case ( Position ( MyField ; "[0" ; 1; 1 ) ; Position ( MyField ; "[0" ; 1; 1 ) ;

Position ( MyField ; "[1" ; 1; 1 ) ; Position ( MyField ; "[1" ; 1; 1 ) ;

Position ( MyField ; "[2" ; 1; 1 ) ; Position ( MyField ; "[2" ; 1; 1 ) ;

Position ( MyField ; "[3" ; 1; 1 ) ; Position ( MyField ; "[3" ; 1; 1 ) ;

Position ( MyField ; "[4" ; 1; 1 ) ; Position ( MyField ; "[4" ; 1; 1 ) ;

Position ( MyField ; "[5" ; 1; 1 ) ; Position ( MyField ; "[5" ; 1; 1 ) ;

Position ( MyField ; "[6" ; 1; 1 ) ; Position ( MyField ; "[6" ; 1; 1 ) ;

Position ( MyField ; "[7" ; 1; 1 ) ; Position ( MyField ; "[7" ; 1; 1 ) ;

Position ( MyField ; "[8" ; 1; 1 ) ; Position ( MyField ; "[8" ; 1; 1 ) ;

Position ( MyField ; "[9" ; 1; 1 ) ; Position ( MyField ; "[9" ; 1; 1 ) ) ;



$EndItem = 

Case ( Position ( MyField ; "0]" ; 1; 1 ) ; Position ( MyField ; "[0" ; 1; 1 ) ;

Position ( MyField ; "1]" ; 1; 1 ) ; Position ( MyField ; "1]" ; 1; 1 ) ;

Position ( MyField ; "2]" ; 1; 1 ) ; Position ( MyField ; "2]" ; 1; 1 ) ;

Position ( MyField ; "3]" ; 1; 1 ) ; Position ( MyField ; "3]" ; 1; 1 ) ;

Position ( MyField ; "4]" ; 1; 1 ) ; Position ( MyField ; "4]" ; 1; 1 ) ;

Position ( MyField ; "5]" ; 1; 1 ) ; Position ( MyField ; "5]" ; 1; 1 ) ;

Position ( MyField ; "6]" ; 1; 1 ) ; Position ( MyField ; "6]" ; 1; 1 ) ;

Position ( MyField ; "7]" ; 1; 1 ) ; Position ( MyField ; "7]" ; 1; 1 ) ;

Position ( MyField ; "8]" ; 1; 1 ) ; Position ( MyField ; "8]" ; 1; 1 ) ;

Position ( MyField ; "9]" ; 1; 1 ) ; Position ( MyField ; "9]" ; 1; 1 ) ) ] ;







If ( $EndItem - $StartItem = 7 ; Middle ( MyField ; $StartItem + 1 ; 7 ) ; "" )

 )

Thanks for any advice or assistance

ZP

[Raybaudi]

Hi

could that field contain other numbers ?

If there is ALWAYS only one number into the text, all you'll need is:

GetAsNumber ( MyField )

If that number can have one o more initial zero, you can try:

Filter ( MyField ; 1234567890 )

BTW: There are many other ways to have reliable results even if there can be more than one number.

Something like this:

Let([

adj = Substitute ( MyField

; [ "[0" ; "§0" ]

; [ "[1" ; "§1" ]

; [ "[2" ; "§2" ]

; [ "[3" ; "§3" ]

; [ "[4" ; "§4" ]

; [ "[5" ; "§5" ]

; [ "[6" ; "§6" ]

; [ "[7" ; "§7" ]

; [ "[8" ; "§8" ]

; [ "[9" ; "§9" ]

; [ "0]" ; "0§" ]

; [ "1]" ; "1§" ]

; [ "2]" ; "2§" ]

; [ "3]" ; "3§" ]

; [ "4]" ; "4§" ]

; [ "5]" ; "5§" ]

; [ "6]" ; "6§" ]

; [ "7]" ; "7§" ]

; [ "8]" ; "8§" ]

; [ "9]" ; "9§" ]

) ;

start = Position ( adj ; "§" ; 1 ; 1 ) + 1 ;

end = Position ( adj ; "§" ; 1 ; 2 )

];

Middle ( adj ; start ; end - start )

)

[comment]

Try:

Let ( [

mask = Substitute ( text ;

[ "1" ; "0" ] ;

[ "2" ; "0" ] ;

[ "3" ; "0" ] ;

[ "4" ; "0" ] ;

[ "5" ; "0" ] ;

[ "6" ; "0" ] ;

[ "7" ; "0" ] ;

[ "8" ; "0" ] ;

[ "9" ; "0" ]

) ;

start = Position ( mask ; "[0000000]" ; 1 ; 1 ) ;

end = Position ( text ; "]" ; start ; 1 )

];

Case ( start ; Middle ( text ; start + 1 ; end - start - 1 ) )

)

This should extract the first occurrence of 7 consecutive digits surrounded by brackets in text.

[Raybaudi]

This is another one:

Let([

a = Substitute ( Filter ( MyField ; " 0123456789[]" ) ; " " ; ¶ ) ;

b = Substitute ( MyField ; " " ; ¶ ) ;

c = FilterValues ( a ; b ) ;

start = Position ( c ; "[" ; 1 ; 1 ) + 1;

end = Position ( c ; "]" ; 1 ; 1 )

];

Middle ( c ; start ; end - start )

)

Even this ( as my first calc ) does not check for digit's number.

[comment]

And if the code is not surrounded by spaces, e.g. "My letter to Smith [1234567], sent on ... "?

BTW, my way can be simplified to:

Let ( [

mask = Substitute ( text ;

[ "1" ; "0" ] ;

[ "2" ; "0" ] ;

[ "3" ; "0" ] ;

[ "4" ; "0" ] ;

[ "5" ; "0" ] ;

[ "6" ; "0" ] ;

[ "7" ; "0" ] ;

[ "8" ; "0" ] ;

[ "9" ; "0" ]

) ;

start = Position ( mask ; "[0000000]" ; 1 ; 1 )

];

Case ( start ; Middle ( text ; start + 1 ; 7 ) )

)

Edited June 26, 2009 by Guest
Doh

[Raybaudi]

good point .. the calc will fail.

I was searching a way to not use so many substitutions.

BTW: this corrects that bug

Let([

a = Substitute ( MyField ;[ "[" ; "¶[" ] ; [ "]" ; "]¶" ] ) ;

b = Substitute ( Filter ( a ; " 0123456789[]" ) ; " " ; ¶ )

];

Filter ( FilterValues ( a ; b ) ; 1234567890 )

)

... and has only 3 substitutions.

[Zippy Aus]

AWESOME ... thanks for the assistance.

I have used the mask solution as it seems to pull out the number as required.

Thanks again