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

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

If  Regex.IsMatch(Add_L2, "^[0-9 ]") then.....

IsNum= int.TryParse(Add_L2, out [whatever var you want to return ) If IsNum then....

Also, don't forget to check the string lengths when adding Address Line 2 to Address Line 1 - wouldn't want a long Address Line 2 to cause the total length to be greater than the total allowable Address Line 1 length

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?
 

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