9 Replies Latest reply: May 15, 2014 10:01 AM by Bill Watson RSS

    Proper() function

    Brad Shipman

      When using this function on numbers such as:

       

      2ND

      1ST

      3RD

      4TH

       

      and so on, this function returns

       

      2Nd

      1St

      3Rd

      4Th

       

      When using ASAP utilities in Excel, the proper casing function recognizes that the letters should all be lower case.  Is there any way to get Monarch to do the same?

        • Proper() function
          Grant Perkins

          Hi Brad,

           

          Interesting observation which prompts me to consider whether I have ever used the Proper() function and I don't think I have.

           

          If you need a quick fix I guess you could put together a User Defined Function that  would check for  a numeric value followed by one of a list of  abbreviations and applied selective processing  if found but otherwise just used the Proper() function. However I can envisage some potential challenges for that idea if used on large blocks of text, though it could work quite well on address lines I would think.

           

          Does this help at all?

           

           

           

          Grant

           

          Edit: Actually thinking about it the UDF might first set the Proper() changes as they are and then check for anomalies in the resulting text and change them as required. As I said, there could be a few challenges.

          • Proper() function
            Nigel Winton

            Brad

            The Lower() function will give you the results you require, you could always set it up to check for a number in the first position and then apply the Lower() or use Proper() elsewhere.

             

            if( .Not. IsAlpha(left(field1,1)),lower(field1),proper(field1)) should give the desired result.

             

            Regards

             

            Nigel

              • Proper() function
                Bill Watson

                Sorry for showing up late on this.. the following would solve your problem for any string combination you have with this number notation:

                 

                replace(replace(replace(replace(replace(replace(replace(replace(replace(Replace(Proper(Field1),"1St","1st"),"2Nd","2nd"),"3Rd","3rd"),"4Th","4th"),"5Th","5th"),"6Th","6th"),"7Th","7th"),"8Th","8th"),"9Th","9th"),"0Th","0th")

                 

                My Example Raw Date and Output

                 

                Field1          Field2

                22 22ND STREET     22 22nd Street

                1ST AVENUE     1st Avenue

                3RD DEGREE     3rd Degree

                4TH WALL     4th Wall

                20TH FLOOR     20th Floor

                /code

                  • Proper() function
                    Data Kruncher

                    I'm not sure that you'd want to use this on large blocks, but you should be OK with conventional single line addresses.

                     

                    Bill's solution will fix the initial problem with the numbers and text, but doesn't provide the "proper" text for the street names and other suffixes as displayed his sample solution.

                     

                    Here's my kick at it, FWIW.

                     

                    Based on the inital address being named Addr, build a NumberPosition numeric field with the expression:

                    [SIZE=2]instr("1st",Addr)instr("2nd",Addr)instr("3rd",Addr)instr("4th",Addr)instr("5th",Addr)instr("6th",Addr)instr("8th",Addr)instr("9th",Addr)instr("0th",Addr)[/SIZE][/code]

                     

                    Now build a ProperAddress character field with this expression:

                    [SIZE=2]If(=0,Addr,Left(Addr,[NumberPosition]-1)Lower(Substr(Addr,[NumberPosition],3))Proper(Substr(Addr,[NumberPosition]+3,255)))[/SIZE][/code]

                     

                    This will leave the leftmost part of the initial address unchanged (though you could Proper it), fix the #xx stuff, and Proper whatever comes after that.

                     

                    This will give you the output as shown in Bill's sample.

                     

                    Kruncher

                     

                    Edit: Because of the method used to determine the position of the #xx combination within the field, this approach will blow up nicely when more than one #xx combination exists in a single address, such as "4TH Floor, 555 123RD Street". In such cases you could LSplit the floor and the street data, clean both parts and rebuild it.

                      • Proper() function
                        Nick Osdale-Popa

                        Not sure how helpful this will be, but here's a formula that will return the ordinal position of any number (passed as character):

                         

                        NumberPositionIF((MOD(val(NumberPosition),100)>=10.and. MOD(val(NumberPosition),100)<=14),"th",substr("thstndrdthththththth",(MOD(val(NumberPosition),10)*2)1,2))[/code]

                        • Proper() function
                          Bill Watson

                          Bill's solution will fix the initial problem with the numbers and text, but doesn't provide the "proper" text for the street names and other suffixes as displayed his sample solution.

                          /QUOTE

                           

                          DK

                           

                          maybe i'm missing your point but the output in my example was from my model and would reflect what Proper() SHOULD output

                           

                          I have expanded the formula to account for 11th, 12th, 13th etc

                           

                          replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(Replace(Proper(Field1),"1St" ,"1st"),"1Th","1th"),"2Nd","2nd"),"2Th","2th"),"3Rd","3rd"),"3Th","3th"),"4Th","4th"),"5Th","5th"),"6Th","6th"),"7Th","7th"),"8Th","8th")," 9Th","9th"),"0Th","0th")

                          /code

                           

                           

                          I ran it again on a slightly extended set of data.

                           

                          First as individual records which looks fine to me:

                          BILL WATSON FLAT 1B 10TH FLOOR 22ND STREET ANYTOWN     Bill Watson Flat 1B 10th Floor 22nd Street Anytown

                          BILL WATSON JNR 531 1ST AVENUE ANYCITY               Bill Watson Jnr 531 1st Avenue Anycity

                          3RD DEGREE BURNS                         3rd Degree Burns

                          BREAKING THE 4TH WALL                         Breaking The 4th Wall

                          FRIDAY THE 13TH WAS THE 1ST IN THE SERIES          Friday The 13th Was The 1st In The Series

                          /Code

                           

                          If you pull the whole data set in as a single memo field the following result is given:

                          Bill Watson Flat 1B 10th Floor 22nd Street Anytown Bill Watson Jnr 531 1st Avenue Anycity 3rd Degree Burns Breaking The 4th Wall Friday The 13th Was The 1st In The Series

                          /code

                           

                          I don't think this would break even using a larger dataset but I am happy to be proved wrong.

                            • Proper() function
                              Nick Osdale-Popa

                              Couldn't Bill's formula be shortened to:

                              replace(replace(replace(Replace(Proper(Address),"1St" ,"1st"),"Th ","th "),"Nd ","nd "),"3Rd","3rd")[/code]

                               

                              You're unlikely to find "Nd " "Th " by themselves, whereas you may find "St " for street and "Rd " for road.''

                               

                              I get the same results as Bill with this formula.

                                • Proper() function
                                  Data Kruncher

                                  Nice work gentlemen. :cool:

                                  • Proper() function
                                    Bill Watson

                                    Nick

                                     

                                    You are correct that in english these seldom occur together but I have learned never to underestimate the ingenuity of data inputters and would err on the side of caution by being specific

                                     

                                    Couldn't Bill's formula be shortened to:

                                    replace(replace(replace(Replace(Proper(Address),"1St" ,"1st"),"Th ","th "),"Nd ","nd "),"3Rd","3rd")[/code]

                                     

                                    You're unlikely to find "Nd " "Th " by themselves, whereas you may find "St " for street and "Rd " for road.''

                                     

                                    I get the same results as Bill with this formula.[/QUOTE]