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

# Proper() function

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

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

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

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

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:

Now build a ProperAddress character field with this expression:

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

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'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

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

Nice work gentlemen. :cool:

• ###### Proper() function

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]