16 Replies Latest reply: May 15, 2014 10:09 AM by FrankK _ RSS

    Extract Data

    FrankK _

      Hello,

       

      I'm trying to pull numbers out of my report but they include text.  I know i need to create a formula but not sure what to use.  Here is an example of my report and the inconsistencies it presents.

       

                    Copay: 1.00

      Copay:  1.00

                               1.00

              1.00

                 Copay:  1.00

       

      My model will grab the amounts without "Copay:" but the amounts with "Copay:" return a null value.  Does anyone know a formula i could use to extract this from my dollar amount?  Any help would be appreciated.

       

      Thank you,

       

      Frank

        • Extract Data
          Olly Bond

          Hello Frank,

           

          If you are capturing the whole line:

           

          COPAY: 1.00[/CODE]

           

          as a field, make sure that field is of type Character.

           

          Then the formula val(strip(;strip(;"0123456789.-"))) should do the trick.

           

          Working from the right, the formula strips all digits as well as decimal and negative signs from the character field. It then strips this from whole character field to leave behind just the numeric elements. The val() converts the remaining string "1.00" into a number.

           

          Hope this helps,

           

          Olly

            • Extract Data
              FrankK _

              Hello Olly,

               

              Thank you for the help, it worked great.  Now i have another question regarding the same issue but character related.  I tried using the same formula but modified it a bit but not having much luck.  Here's my example.

               

              SUMMERS,MARJORIE  199 (Continued)   

               

              I want to only include SUMMERS,MARJORIE and strip the number and the "(Continued)".  When i use the strip formula, i'm able to get the name but since Continued has a capital "C", it leaves the "C" and the 199.  However, in some cases with other names that are longer, the number may appear as "1" instead of "199".  It's as if the cell has a limit to the number of characters and cuts it off.  Any ideas?

               

              Frank

                • Extract Data
                  FrankK _

                  I fixed my problem regarding the characters being cut off, now just have the other issue.

                    • Extract Data
                      warlok

                      If the input data is always of similar format (lastname,firstname followed by maybe number and maybe (continued)) then you could use the space as a cutoff using the Instr function

                       

                      Substr(Input,1,Instr(Input," ")-1)

                       

                      Input would be the name of the field. Don't forget the space between the quotes.

                        • Extract Data
                          FrankK _

                          Yes, the data is always in the format you mentioned but will probably always have a number but the (Continued) is a definite maybe.  I tried the formula and it returned a zero value.  I proceded to play around and if i put a +1 instead of  a -1 i would have a result but it wasn't correct.  I just wanted to see if i may have done something wrong.  Any ideas on why it wasn't showing me any data?

                            • Extract Data
                              warlok

                              I made a mistake on the formula and switched the target string with the search string. Here is the corrected line:

                               

                              Substr(Input,1,Instr(" ",Input)-1)

                                • Extract Data
                                  FrankK _

                                  Ok, that now works, except that i found a few people that had a middle initial after their last name so all i see now is the last name.  Example:

                                   

                                  DOE L,JOHN   -   After the formula i only see DOE     Anything we can do about that?  If it wasn't for that, the formula worked perfect.

                                    • Extract Data
                                      Olly Bond

                                      Hello Frank,

                                       

                                      I think we could tackle this by taking everything up to the comma, then everything from the comma to the first space after it.

                                       

                                      extract(;"";",")","extract(;",";" ")

                                       

                                      should do the trick.

                                       

                                      Best wishes,

                                       

                                      Olly

                                      • Extract Data
                                        warlok

                                        I love it when unexpected complications pop up. Hmmm thinking about it, you will also run into issues if the last name is a compound name such as St. John or the first name is compund such as Anna Marie. Perhaps then the better way to tackle this would be to go after the number rather than the space.

                                          • Extract Data
                                            FrankK _

                                            Yeah, have to wonder what some people think when developing reports with inconsistencies.  If we go after the number, won't we still have the (Continued) data?

                                              • Extract Data
                                                warlok

                                                Well Olly's suggestion does get it closer, but it misses if there is middle names involved. There is a few ways to do this that i can see but nothing too straightforward. You'd need to create a temporary holding field that you can hide later to help manipulte the data in the field to be 100% sure you catch all the possabilities. But I could be missing something as well. I'm seeing what i can come up with here for you. I'm sure Olly is taking a second look too if he's online.

                                                  • Extract Data
                                                    FrankK _

                                                    Great, thank you.

                                                      • Extract Data
                                                        Olly Bond

                                                        Hello Frank,

                                                         

                                                        If we know that there's only ever one group of numbers after each name, then we can work from the right.

                                                         

                                                        First, remove the (Continued) (if it appears), with rtrim(extract(;"";"(")) - the rtrim removes any trailing spaces.

                                                         

                                                        Now we assume there's always a number on the right hand side, so we can simply rsplit on space:

                                                         

                                                        rsplit(rstrim(extract(;"";"("));2;" ";2)

                                                         

                                                        Does that solve it?

                                                         

                                                        Best wishes,

                                                         

                                                        Olly

                                                          • Extract Data
                                                            FrankK _

                                                            Well, i found another exception.  I have a couple people that had the their middle initial as follows:

                                                             

                                                            Doe,John (H)

                                                            Doe (H),John

                                                             

                                                            I'm not sure why their middle intial is in backets but either way, all of my records gave me the result i wanted except for a hand full.

                                                    • Extract Data
                                                      warlok

                                                      Okay, I think I have something that will work for you. Try this:

                                                       

                                                      Substr(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Input,"2","1"),"3","1"),"4","1"),"5","1"),"6","1"),"7","1"),"8","1"),"9","1"),"0","1"),1,Instr("1",Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Input,"2","1"),"3","1"),"4","1"),"5","1"),"6","1"),"7","1"),"8","1"),"9","1"),"0","1"))-1)

                                                       

                                                      I convert all numbers to 1. I'm assuming that no numbers will show up in the name, therefore we can key off the first #1 and pull the whole name as listed. Let me know what you think. There may be a shorter way of doing this, but I couldnt think of it on such chort notice.

                                                        • Extract Data
                                                          FrankK _

                                                          Warlok,

                                                           

                                                          This worked perfectly!  Thank you to both you and Olly for the assistance.  It is greatly appreciated.  If you come up wtih something shorter in the formula, feel free to pass along. 

                                                           

                                                          Frank