Good morning everyone! Can any one help with this formula? I have a job header field which contains the invoice number in parentheses. I'd like to extract the invoice number onto a new invoice number field. Thank you.

Good morning everyone! Can any one help with this formula? I have a job header field which contains the invoice number in parentheses. I'd like to extract the invoice number onto a new invoice number field. Thank you.

Please provide a representative sample of the string. There are many ways to parse the text. Many could fail depending on on what other text is in the string.

GetValue ( substitute ( YourTextHere ; [ “(“ ; pilcrow ] ; [ “(“ ; pilcrow ] ) ; 2 )

put in the paragraph symbol where I typed “pilcrow”

but this assumes only 1 pair of parenthesis in your string.

If the source value looks like "(0537621)" you could do it this way:

Let ( sv = "(05R7621)" ; // or use a field reference hereSubstitute (

sv ;

[ "(" ; "" ];

[ ")" ; "" ]

)

)

The "Filter" function might also help you here. Would all depend on exactly what your source value looks like and -- very important -- how reliable the format of that value is.

- 1 person found this helpful
If you have a field called YourField, the calculation below will find the first set of matching parentheses and return what is enclosed. This makes no assumptions about the input string or the string inside parentheses.

-Tom

Let([

~theText = YourField;

~posnOpen = Position(~theText; "("; 1; 1);

~posnClose = Position(~theText; ")"; ~posnOpen+1; 1)

];

Case(

~posnOpen = 0; "";

~posnClose = 0; "";

Middle(~theText; ~posnOpen+1; ~posnClose - ~posnOpen - 1)

)

)

Thank you for the example! There are answers here that get you the Position() in the string for each of the parenthesis. Then it's a matter of using them to get the Length() of that value to extract. Using the Middle() and the calculated positions for start & length should work well.

A gotcha, as this cannot be a number result because your example contains "r" inside those parenthesis.

I wonder if your text has more than one value inside parenthesis. All very important to know to get that calc correct.

Beverly

Sent from miPhone

Your same text shows no returns and no other pairs of parenthesis.

Thus,

GetValue ( substitute ( YourTextHere ; [ “(“ ; ¶ ] ; [ “)“ ; ¶ ] ) ; 2 )

will also work to return the correct value. Just had to make the correction that Peter pointed out.

This makes no assumptions about the input string or the string inside parentheses

No, but it does assume that the invoice number is found inside the first pair of parenthesis, the same assumption made by the approach I suggested. It will, however, work even if the text contains returns where the Substitute/getvalue method that I described will then fail if any returns are located to the left of the ).

So they both work on your examples, use the one that's easiest for you to work with.

If you have a field called YourField, the calculation below will find the first set of matching parentheses and return what is enclosed. This makes no assumptions about the input string or the string inside parentheses.

-Tom

Let([

~theText = YourField;

~posnOpen = Position(~theText; "("; 1; 1);

~posnClose = Position(~theText; ")"; ~posnOpen+1; 1)

];

Case(

~posnOpen = 0; "";

~posnClose = 0; "";

Middle(~theText; ~posnOpen+1; ~posnClose - ~posnOpen - 1)

)

)