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.
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.
~theText = YourField;
~posnOpen = Position(~theText; "("; 1; 1);
~posnClose = Position(~theText; ")"; ~posnOpen+1; 1)
~posnOpen = 0; "";
~posnClose = 0; "";
Middle(~theText; ~posnOpen+1; ~posnClose - ~posnOpen - 1)
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.
quick correction: you'll want a closing parenthesis in the second Substitute ( ) parameter.
EDIT: Also, the success of this formula depends on the absence of carriage returns in the string between the parentheses.
If the source value looks like "(0537621)" you could do it this way:
Let ( sv = "(05R7621)" ; // or use a field reference here
[ "(" ; "" ];
[ ")" ; "" ]
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.
Gods point on the returns and it illustrates why more info about the string is needed, and maybe it’s my morning caffeine deficit, but don’t see any missing parenthesis.
you have two opening parentheses -- "(" -- in your formula, when I think you meant to have an opening and a closing parenthesis.
Thanks Peter. Yes, the second parenthesis in quotes should be “)”. Time to brew a cup.
Filter ( myFieldOrVar ; "0123456789" )
Will give you only those digits anywhere in the "string")
Sample string desired result
Company A Corporate Event 1/11/14 (1603r) 1603r
Thank you very much. Your response works like a charm.
Thank you everyone for your responses. I truly appreciate everyone's willingness to help. My deepest respect to all.
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.
Sent from miPhone
Your same text shows no returns and no other pairs of parenthesis.
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.
Retrieving data ...