10 Replies Latest reply on May 6, 2009 5:14 AM by KIDO

    extract a number between parenthesis in a text field

    Larrydb321

      Title

      extract a number between parenthesis in a text field

      Post

      Sorry for the newbie question. Can anyone help me extract a number between parenthesis in a text field that also contains other numbers?

      ie: in a text field that contains "..67893TYUD(5894589)", I need the result to be "5894589". Not all the fields have the same number of characters, and the number inside the parenthsis can be from 1 to 9 characters.

       

      Any Help Very Appreciated!

      Larry

        • 1. Re: extract a number between parenthesis in a text field
          raybaudi
            

          Hi Larry

           

          If the number to extract is ALWAYS to the very right of your text field, the calculation can be as simple as:

           

          RightWords ( yourTextField ; 1 )

          • 2. Re: extract a number between parenthesis in a text field
            Larrydb321
              

            Good thought! Thank You. Im not sure if the number will alway be at the right, but for now that works.

             

            Larry

            • 3. Re: extract a number between parenthesis in a text field
              raybaudi
                

              Otherwise, if the text field can contains strings like these:

               

              67893TYUD( 5894589 ) 99999
              or
              67893TYUD(5894589)A9999

               

              it is better this calculation:

               

              Let([
              start = Position ( yourTextField; "(" ; 1 ; 1 ) + 1 ;
              end = Position ( yourTextField ; ")" ; 1 ; 1 )
              ];
              Trim ( Middle ( yourTextField ; start ; end - start ) )
              )


              • 4. Re: extract a number between parenthesis in a text field
                KIDO
                  

                Hi Larry

                 

                You must try the following expression. Replace the field "z_text_o" with your text field name and you'll be OK, no matter where in the field the parenthesis are located, as long as the characters "(" and ")" have no more than 1 occurrence each in each record. - You may use it as a auto-enter calculation in a field or in a script.

                 

                GetAsNumber ( Middle ( z_text_o ; Position (z_text_o ; "(" ; 1 ; 1 ) ; Position ( z_text_o ; ")" ; 1 ; 1 ) - Position ( z_text_o ; "(" ; 1 ; 1)))

                 

                Have fun

                 

                KIDO

                • 5. Re: extract a number between parenthesis in a text field
                  raybaudi
                    

                  KIDO wrote:

                   

                   

                  GetAsNumber ( Middle ( z_text_o ; Position (z_text_o ; "(" ; 1 ; 1 ) ; Position ( z_text_o ; ")" ; 1 ; 1 ) - Position ( z_text_o ; "(" ; 1 ; 1)))

                   


                  The lone difference from your calculation and mine is that you used GetAsNumber( ) instead of Trim( ).

                   

                  But in that way your calc will fail if the number starts with 0.


                  • 6. Re: extract a number between parenthesis in a text field
                    KIDO
                      

                    Hi Daniele

                     

                    If leading zeros are important and text format desirable, the expression can be reduced as follows:

                     

                    <!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:View&gt;Normal&lt;/w:View&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:PunctuationKerning/&gt;   &lt;w:ValidateAgainstSchemas/&gt;   &lt;w:SaveIfXMLInvalid&gt;false&lt;/w:SaveIfXMLInvalid&gt;   &lt;w:IgnoreMixedContent&gt;false&lt;/w:IgnoreMixedContent&gt;   &lt;w:AlwaysShowPlaceholderText&gt;false&lt;/w:AlwaysShowPlaceholderText&gt;   &lt;w:Compatibility&gt;    &lt;w:BreakWrappedTables/&gt;    &lt;w:SnapToGridInCell/&gt;    &lt;w:WrapTextWithPunct/&gt;    &lt;w:UseAsianBreakRules/&gt;    &lt;w:DontGrowAutofit/&gt;   &lt;/w:Compatibility&gt;   &lt;w:BrowserLevel&gt;MicrosoftInternetExplorer4&lt;/w:BrowserLevel&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:LatentStyles DefLockedState=&quot;false&quot; LatentStyleCount=&quot;156&quot;&gt; &lt;/w:LatentStyles&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:&quot;Times New Roman&quot;;      mso-ansi-language:EN-GB;} @page Section1      {size:612.0pt 792.0pt;      margin:72.0pt 90.0pt 72.0pt 90.0pt;      mso-header-margin:36.0pt;      mso-footer-margin:36.0pt;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 10]&gt; &lt;style&gt; /* Style Definitions */ table.MsoNormalTable      {mso-style-name:&quot;Table Normal&quot;;      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:&quot;&quot;;      mso-padding-alt:0cm 5.4pt 0cm 5.4pt;      mso-para-margin:0cm;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:&quot;Times New Roman&quot;;      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} &lt;/style&gt; &lt;![endif]  -->                               Middle ( z_text_o ; Position (z_text_o ; "(" ; 1 ; 1 )  + 1; Position ( z_text_o ; ")" ; 1 ; 1 ) - Position ( z_text_o ; "(" ; 1 ; 1)  - 1) 

                     

                    The underlined bolded +1 and -1 are necessary to get rid of the parenthesis.

                     

                     

                    KIDO

                    • 7. Re: extract a number between parenthesis in a text field
                      raybaudi
                        

                      This one has problems, too.

                       

                      What if the text field contains:

                       

                      67893TYUD( 5894589 ) 99999

                       

                      It will give some spurious spaces... so the need of Trim ( ) as I wrote in my calculation.

                      • 8. Re: extract a number between parenthesis in a text field
                        KIDO
                           <!-- [if gte mso 9]><xml> <w:WordDocument>   <w:View>Normal</w:View>   <w:Zoom>0</w:Zoom>   <w:PunctuationKerning/>   <w:ValidateAgainstSchemas/>   <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>   <w:IgnoreMixedContent>false</w:IgnoreMixedContent>   <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>   <w:Compatibility>    <w:BreakWrappedTables/>    <w:SnapToGridInCell/>    <w:WrapTextWithPunct/>    <w:UseAsianBreakRules/>    <w:DontGrowAutofit/>   </w:Compatibility>   <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif] --><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif] --><!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:"";      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:"Times New Roman";      mso-fareast-font-family:"Times New Roman";      mso-ansi-language:EN-GB;} @page Section1      {size:595.3pt 841.9pt;      margin:72.0pt 90.0pt 72.0pt 90.0pt;      mso-header-margin:35.4pt;      mso-footer-margin:35.4pt;      mso-paper-source:0;} div.Section1      {page:Section1;} --><!-- [if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable      {mso-style-name:"Table Normal";      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:"";      mso-padding-alt:0cm 5.4pt 0cm 5.4pt;      mso-para-margin:0cm;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:"Times New Roman";      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} </style> <![endif] -->

                        Hi Daniele

                         

                        There are an infinite number of possibilities if we start questioning “what if?”, and probably no perfect solution!

                         

                        What if the field contains (12 3 456 7 8 9 ) spaces in between?

                         

                        What if the field contains a reference (#) to a subpart code like (123456#789) ? or (123456  #789)?

                         

                        What if … Every possible mistake typed into the field?

                         

                        Larry already accepted your solution; I only offered an alternative considering that leading zeros was not an issue.

                         

                        Let’s not waste our time over it and move forward to a better co-operation.  However, a good way to practice English (although my English is not good either).

                         

                        Best regards

                         

                        KIDO

                        • 9. Re: extract a number between parenthesis in a text field
                          Larrydb321
                             Good Plan!!! Thanks for both of your help. I have actually included both fields in my db just to compare when Im importing data. So far, both have worked for everything. Thanks again.