topic Re: How to extract only numeric values out of text string in Discussions

topic Re: How to extract only numeric values out of text string in Discussions https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/8419#M8413 <HTML><HEAD></HEAD><BODY><P>Your solution worked perfectly also.   </P><P>I learnt something new with regards to escape string/delimiters. Thanks !</P></BODY></HTML> Fri, 18 Apr 2014 17:44:27 GMT jayv_ 2014-04-18T17:44:27Z How to extract only numeric values out of text string https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/8414#M8408 <HTML><HEAD></HEAD><BODY><P>I have a large data table which has a  column containing char text.    An example of the text  description is  listed below.</P><P>I need to do some descriptive statistics on the duration to open/close for the robot.  </P><P>I would like to extract the numeric portion of the character string and put it in a new column with numeric data type.</P><P>Is there a formula function I could use to do that  on a brand new column ?</P><P></P><P>Sample text</P><P></P><P> : Valve Movement to "CLOSE" position Completed. Time Taken for Movement "1.530000" Secs</P><P> : Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.030000" Secs</P><P> : Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.010000" Secs</P><P> : Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.510000" Secs</P><P> : Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.215000" Secs</P><P> : Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.215000" Secs</P><P> : Valve Movement to "OPEN" position Completed. Time Taken for Movement "3.160000" Secs</P><P> : Valve Movement to "OPEN" position Completed. Time Taken for Movement "3.160000" Secs</P><P> : Valve Movement to "CLOSE" position Completed. Time Taken for Movement "1.815000" Secs</P><P> : Valve Movement to "CLOSE" position Completed. Time Taken for Movement "1.815000" Secs</P><P> : Valve Movement to "OPEN" position Completed. Time Taken for Movement "3.140000" Secs</P><P> : Valve Movement to "OPEN" position Completed. Time Taken for Movement "3.140000" Secs</P><P> : Valve Movement to "OPEN" position Completed. Time Taken for Movement "2.955000" Secs</P></BODY></HTML> Thu, 17 Apr 2014 23:18:30 GMT https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/8414#M8408 jayv_ 2014-04-17T23:18:30Z Re: How to extract only numeric values out of text string https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/8415#M8409 <HTML><HEAD></HEAD><BODY><P>Depends upon EXACTLY what you want.  Does the following come close?</P><P></P><P>data have;</P><P>   input string & $100.;</P><P>   cards; </P><P>Valve Movement to "CLOSE" position Completed. Time Taken for Movement "1.530000" Secs</P><P>: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.030000" Secs</P><P>: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.010000" Secs</P><P>: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.510000" Secs</P><P>: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.215000" Secs</P><P>: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.215000" Secs</P><P>: Valve Movement to "OPEN" position Completed. Time Taken for Movement "3.160000" Secs</P><P>: Valve Movement to "OPEN" position Completed. Time Taken for Movement "3.160000" Secs</P><P>: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "1.815000" Secs</P><P>: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "1.815000" Secs</P><P>: Valve Movement to "OPEN" position Completed. Time Taken for Movement "3.140000" Secs</P><P>: Valve Movement to "OPEN" position Completed. Time Taken for Movement "3.140000" Secs</P><P>: Valve Movement to "OPEN" position Completed. Time Taken for Movement "2.955000" Secs</P><P>;</P><P>data want (drop=string both);</P><P>  set have;</P><P>  both=compress(substr(string,anydigit(string)),'"');</P><P>  num=input(substr(both,1,anyalpha(both)-1),12.);</P><P>  unit=substr(both,anyalpha(both));</P><P>run;</P></BODY></HTML> Fri, 18 Apr 2014 00:14:42 GMT https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/8415#M8409 2014-04-18T00:14:42Z Re: How to extract only numeric values out of text string https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/8416#M8410 <HTML><HEAD></HEAD><BODY><P>Arthur, </P><P></P><P>     Thank you for your reply.   I started reading the Char functions help manual.</P><P>     I applied the following formula on my text column  which seemed to work.</P><P></P><P>     Num(Substr(Word(12, :Text), 2, 8))</P><P></P><P>     a) Extract  12th word from Text string   (i.e. "1.530000")</P><P>     b) Extract substring between quotations  (i.e. 1.530000)</P><P>     c) Convert character to numeric using Num function  (i.e. 1.53)</P></BODY></HTML> Fri, 18 Apr 2014 00:25:26 GMT https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/8416#M8410 jayv_ 2014-04-18T00:25:26Z Re: How to extract only numeric values out of text string https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/8417#M8411 <HTML><HEAD></HEAD><BODY><P>Try the scan function with the " as your delimiter.</P><P></P><P>scan(string, 4, '"')</P></BODY></HTML> Fri, 18 Apr 2014 00:30:41 GMT https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/8417#M8411 reeza 2014-04-18T00:30:41Z Re: How to extract only numeric values out of text string https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/8418#M8412 <HTML><HEAD></HEAD><BODY><P>Hi JayV.,</P><P></P><P>The Word() function will also take an argument to specify the delimiters, so you can avoid the Substr() part of your formula and just use:</P><P></P><P>   Num( Word( 11, :Text, "\!" " ) )</P><P></P><P>The \! is the escape string to tell the parser that the " is part of the quoted string.</P><P></P><P>-Jeff</P></BODY></HTML> Fri, 18 Apr 2014 12:43:55 GMT https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/8418#M8412 Jeff_Perkinson 2014-04-18T12:43:55Z Re: How to extract only numeric values out of text string https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/8419#M8413 <HTML><HEAD></HEAD><BODY><P>Your solution worked perfectly also.   </P><P>I learnt something new with regards to escape string/delimiters. Thanks !</P></BODY></HTML> Fri, 18 Apr 2014 17:44:27 GMT https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/8419#M8413 jayv_ 2014-04-18T17:44:27Z Re: How to extract only numeric values out of text string https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/65078#M34294 <P>Hi I have a need to extract X & Y values from a text string & separate column, whats the formula? Thanks!</P><P> </P><TABLE><TBODY><TR><TD>SITEPOS</TD></TR><TR><TD>X24Y34</TD></TR><TR><TD>X24Y35</TD></TR><TR><TD>X24Y36</TD></TR><TR><TD>X24Y37</TD></TR><TR><TD>X24Y38</TD></TR><TR><TD>X24Y39</TD></TR><TR><TD>X25Y34</TD></TR><TR><TD>X25Y35</TD></TR><TR><TD>X25Y36</TD></TR><TR><TD>X25Y37</TD></TR><TR><TD>X25Y38</TD></TR><TR><TD>X25Y39</TD></TR><TR><TD>X26Y33</TD></TR><TR><TD>X26Y34</TD></TR><TR><TD>X26Y35</TD></TR><TR><TD>X26Y36</TD></TR><TR><TD>X26Y37</TD></TR><TR><TD>X26Y38</TD></TR><TR><TD>X27Y33</TD></TR><TR><TD>X27Y34</TD></TR><TR><TD>X27Y35</TD></TR></TBODY></TABLE> Tue, 31 Jul 2018 01:34:55 GMT https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/65078#M34294 jerryspilTC 2018-07-31T01:34:55Z Re: How to extract only numeric values out of text string https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/65097#M34295 <P>Can we assume that the X and Y values are always two digits? If so, here is the formula for the X:</P> <P>Num(Substr(:SITEPOS, 2, 2))</P> <P> </P> <P>And here is the formula for the Y:</P> <P>Num(Substr(:SITEPOS, 5))</P> <P> </P> <P>Note that I converted both the X and Y values into numbers rather than leaving them as text.</P> <P> </P> Tue, 31 Jul 2018 01:57:38 GMT https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/65097#M34295 Dan_Obermiller 2018-07-31T01:57:38Z Re: How to extract only numeric values out of text string https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/65098#M34296 <P>Hi Dan,</P><P>Thanks for prompt response. I have a mix of 2 & single digit sandwich inside the string.</P><P> </P><TABLE><TBODY><TR><TD>SITEPOS</TD></TR><TR><TD>X14Y1</TD></TR><TR><TD>X14Y2</TD></TR><TR><TD>X14Y3</TD></TR><TR><TD>X14Y4</TD></TR><TR><TD>X14Y5</TD></TR><TR><TD>X14Y6</TD></TR><TR><TD>X15Y1</TD></TR><TR><TD>X15Y2</TD></TR><TR><TD>X15Y3</TD></TR><TR><TD>X15Y4</TD></TR><TR><TD>X15Y5</TD></TR><TR><TD>X15Y6</TD></TR><TR><TD>X0Y20</TD></TR><TR><TD>X0Y21</TD></TR><TR><TD>X0Y22</TD></TR><TR><TD>X0Y23</TD></TR><TR><TD>X0Y24</TD></TR><TR><TD>X0Y25</TD></TR><TR><TD>X1Y20</TD></TR><TR><TD>X1Y21</TD></TR><TR><TD>X1Y22</TD></TR><TR><TD>X1Y23</TD></TR><TR><TD>X1Y24</TD></TR><TR><TD>X1Y25</TD></TR><TR><TD>X18Y20</TD></TR><TR><TD>X18Y21</TD></TR><TR><TD>X18Y22</TD></TR><TR><TD>X18Y23</TD></TR><TR><TD>X18Y24</TD></TR><TR><TD>X18Y25</TD></TR><TR><TD>X19Y20</TD></TR><TR><TD>X19Y21</TD></TR><TR><TD>X19Y22</TD></TR><TR><TD>X19Y23</TD></TR></TBODY></TABLE> Tue, 31 Jul 2018 02:01:38 GMT https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/65098#M34296 jerryspilTC 2018-07-31T02:01:38Z Re: How to extract only numeric values out of text string https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/65099#M34297 <P>Hi Dan,</P><P>Thanks for prompt response. I have a mix of 2 & single digit sandwich inside the string.</P><P> </P><TABLE><TBODY><TR><TD>SITEPOS</TD></TR><TR><TD>X14Y1</TD></TR><TR><TD>X14Y2</TD></TR><TR><TD>X14Y3</TD></TR><TR><TD>X14Y4</TD></TR><TR><TD>X14Y5</TD></TR><TR><TD>X14Y6</TD></TR><TR><TD>X15Y1</TD></TR><TR><TD>X15Y2</TD></TR><TR><TD>X15Y3</TD></TR><TR><TD>X15Y4</TD></TR><TR><TD>X15Y5</TD></TR><TR><TD>X15Y6</TD></TR><TR><TD>X0Y20</TD></TR><TR><TD>X0Y21</TD></TR><TR><TD>X0Y22</TD></TR><TR><TD>X0Y23</TD></TR><TR><TD>X0Y24</TD></TR><TR><TD>X0Y25</TD></TR><TR><TD>X1Y20</TD></TR><TR><TD>X1Y21</TD></TR><TR><TD>X1Y22</TD></TR><TR><TD>X1Y23</TD></TR><TR><TD>X1Y24</TD></TR><TR><TD>X1Y25</TD></TR><TR><TD>X18Y20</TD></TR><TR><TD>X18Y21</TD></TR><TR><TD>X18Y22</TD></TR><TR><TD>X18Y23</TD></TR><TR><TD>X18Y24</TD></TR><TR><TD>X18Y25</TD></TR><TR><TD>X19Y20</TD></TR><TR><TD>X19Y21</TD></TR><TR><TD>X19Y22</TD></TR><TR><TD>X19Y23</TD></TR></TBODY></TABLE> Tue, 31 Jul 2018 02:01:41 GMT https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/65099#M34297 jerryspilTC 2018-07-31T02:01:41Z Re: How to extract only numeric values out of text string https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/65100#M34298 <P>This will work for the variable lengths</P> <PRE><CODE class=" language-jsl">Num( Word( 1, SITEPOS, "XY" ) ); // and Num( Word( 2, SITEPOS, "XY" ) );</CODE></PRE> Tue, 31 Jul 2018 02:23:20 GMT https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/65100#M34298 txnelson 2018-07-31T02:23:20Z Re: How to extract only numeric values out of text string https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/65125#M34300 <P>Thank you :)</img></P> Tue, 31 Jul 2018 03:37:13 GMT https://community.jmp.com/t5/Discussions/How-to-extract-only-numeric-values-out-of-text-string/m-p/65125#M34300 jerryspilTC 2018-07-31T03:37:13Z