Start a new topic

## Checking Address Lines for numeric text

I want to create custom code that checks Address Line 2 and if it contains only a number moves it to the beginning of Address Line 1, i.e.

123 Main St

23

would become

23-123 Main St

I think I know how to do everything except check that Address Line 2 is a number.  Any suggestons?

Thanks,

Sandy

I don't want to move the contents of Address Line 2 to Address Line 1 if it contains things like "RR 1", "PO Box 123". I only want to move the contents if it begins with things like "#", followed by all numbers or "Unit " followed by all numbers., etc.

There are a few ways to approach - none would be 100% perfect. I am curious why this is an issue to begin with but that is probably a whole different topic.

First I would suggest you do an analysis of your historical data and identify those 'patterns' which fit 95%+ of your cases - you will never get to 100% as someone can always type something unexpected.  You can then create numerous cases as you listed above to account for each,.

An alternate solution may be based on a general assumption or two (yes, assumptions are bad but may save some work),  You could assume if the length of Address 2 is less than the length Address 1 and Address 2  starts with a number that Address 2 should come before Address 1 (ex. Address 2 = '123', Address 1="Main Street") otherwise put Address 2 behind Address 1 (ex. Address 2 ='Apt #123', Address 1='Main Street' - again , you need to know the diversity of your data set.

I realized I actually have 2 scenarios. 1st) The house # is on the 1st line followed by the street name on the 2nd line.  In that case this formula should work..

If IsNumeric(Import.Fields.GetByName("Street1"))
If Import.Fields.GetByName("Street2") IsNot Nothing Then
Import.Fields.GetByName("Street1").value =String.Concat(Import.Fields.GetByName("Street1")," ",Import.Fields.GetByName("Street2"))
Import.Fields.GetByName("Street2").value = ""
End If
End If

2nd) The Suite/Unit/Apt. is on the 2nd line so then this should work...

If IsNumeric(Import.Fields.GetByName("Street2"))
If Import.Fields.GetByName("Street1") IsNot Nothing Then
Import.Fields.GetByName("Street1").value =String.Concat(Import.Fields.GetByName("Street2"),"-",Import.Fields.GetByName("Street1"))
Import.Fields.GetByName("Street2").value = ""
End If
End If

Any suggestions if I wanted to test for key words like "#", "Unit", "Apt", "Suite" followed by only numbers?

Sandy, am sure others may have alternate solutions however here are a few that come to mind - please ignore typo's as this is just off the top of my head.

Assume Add_L2 is the local var which contains the string value of 'Address Line 2'

If IsNumeric(Add_L2) then ....... note: this may also count the +,-,\$ symbols as valid 