SplitAddresslineHousenumber_CF ( AddressLineWithNumber ; ResultStreetNumberFormat )

Splits the numeric part (House number) from the Street. Separate Fields will be required in the EU by about 2025 for Bank Payments. So FileMaker Developers will need to help their customer to split the address lines. The CF also tries to handle address lines that begin with a number such as 20, Rue de la Gare

Be the first to rate this function Log in to vote

Alexis Gehrt - Show more from this author
Database Designs
https://www.database-designs.ch/

Sample input:

SplitAddresslineHousenumber_CF ("Via Cuolm Liung 15b" ; "Street,Number,List")

Sample output:

Via Cuolm Liung¶15b

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

// Custom Function: SplitAddresslineHousenumber_CF
// Parameters:      Addressline with House Number (text)
//                  The House Number may be at the end or the beginning or may be completely missing

//                  ResultStreetNumberDebug
//                  Pass the parameter "Street", "Number", "Pipe" |, List ¶ Separated, "Debug" - if empty, the original Input String is returned

// Description:     Separates the street from the house number in the given address
// Author:          Alexis Gehrt   alexis@database-designs.ch
// Notes:           Well, if you find smarter options, you're welcome ;-)
// Version:         Dec 28, 2023 - First Release 1.0

// Sample Streets:  Bahnhofstrasse 1
//                  Hans Musterstr. 14c
//                  1024, Oakwood Avenue
//                  20, Rue Des Corbieres
//                  Sil Platz
//                  Via Pianazzo, 5

Let ([
                             // Trick: RightWords removes all unwatend Characters such as Carriage Return 
                             // Usually the german street abbreviation is cleaned - however, double ss is a Swiss form of writing
~addressPreClened =          Trim(RightWords( Substitute (AddressLineWithNumber ; "str." ; "strasse") ; 999));

                             // Find the position of a numeric first digit in the address Character 0123456789
~ValidNummberDigits =        "0123456789";
                             // There are adresses that do not have any numbers at all.
~HasNumberChars =            If (not IsEmpty(Filter ( ~addressPreClened ; ~ValidNummberDigits )) ; 1 ; 0 );
~firstNumberChar =           If (~HasNumberChars = 1 ; Left(Filter ( ~addressPreClened ; ~ValidNummberDigits );1);"");
~firstNumberPosition =       Position (~addressPreClened ; ~firstNumberChar ; 1 ; 1 );

                             // Determine the Position of any Space or Comma after the first digit. This will solve "25a, Rue de la Nation" in France
                             // Note the SortValue by -2 (negative) will sort Numeric descending i.e. if there ist comma, space..  The last Position is used
~firstSpaceOrCommaAfterDigit = GetValue (
                             SortValues (
                             UniqueValues(
                             Position (~addressPreClened ; " " ; ~firstNumberPosition ; 1 ) & "¶" &
                             Position (~addressPreClened ; "," ; ~firstNumberPosition ; 1 )  ; 2  ) ; -2) ;1) ;



                            // Find the first Position of any of the Characters in the Alphabet…
                            // Might need to be Extended by more Umlaut Characters as you wish
~ValidAlphaDigits =          "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZöäüéàèëçÖÄÜÇ" ;
~HasAlphaChars =             If (not IsEmpty(Filter ( ~addressPreClened ; ~ValidAlphaDigits )) ; 1 ; 0 );
~firstAlphaChar =            If (~HasAlphaChars = 1 ; Left(Filter ( ~addressPreClened ; ~ValidAlphaDigits );1);"");
~firstAlphaPosition =        Position ( Upper(~addressPreClened) ; ~firstAlphaChar ; 1 ; 1 ) ;
                             
                            // Check if the first numeric digit is prior to any alpha digit
~isHouseNumberAtBeginning = (~firstNumberPosition > 0) and (~firstNumberPosition < ~firstAlphaPosition) ;
    
                            // Extract the house number and street based on the position of the digits
~houseNumberAllCharacters = If (~HasNumberChars = 0 ; "" ;
                            If (~isHouseNumberAtBeginning;
                            Middle (~addressPreClened ; ~firstNumberPosition ; ~firstSpaceOrCommaAfterDigit - ~firstNumberPosition );
                            Middle (~addressPreClened; ~firstNumberPosition; 9999 ))
                            );
                            // Trim it to eliminate any remaining garbage
~houseNumberCleaned =       Trim( If ( Right(Trim(~houseNumberAllCharacters); 1) = ","; Left(Trim(~houseNumberAllCharacters); Length(Trim(~houseNumberAllCharacters))-1) ; ~houseNumberAllCharacters ))  ;
                            // The street is everything else, so just substitute the extracted housenumber (pre-garbage cleaned) 
~street =                   Trim(Substitute (~addressPreClened ; ~houseNumberAllCharacters ; "" ));
                            // More Clening Options
~streetCleaned =            Case(
                            Right ( ~street ; 1 ) = "," ; Left (~street; Length(~street) -1) ; ~street)
                            ];
                            // End of Let Return the house number and street as a list
Case(
ResultStreetNumberFormat   = "Street" ; ~streetCleaned ;
ResultStreetNumberFormat   = "Number" ; ~houseNumberCleaned; 
ResultStreetNumberFormat   = "Pipe" ; ~streetCleaned & "|" & ~houseNumberCleaned; 
ResultStreetNumberFormat   = "List" ; ~streetCleaned & "¶" & ~houseNumberCleaned; 
ResultStreetNumberFormat   = "Debug" ;  TextColor ( ~streetCleaned ; RGB ( 40 ; 140 ; 40 ) )  & " " & TextColor ( ~houseNumberCleaned  ; RGB ( 255 ; 0 ; 0 ) ) ; AddressLineWithNumber )

)

The function checks for numeric characters and alpha characters in the string/addressline. Such as "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZöäüéàèëçÖÄÜÇ"
Then it determines if the numeric part precedes the string, or follows the string and then cuts the address line.

Sample Street lines are:
Bahnhofstrasse 1
Hans Musterstr. 14c
1024, Oakwood Avenue
20, Rue Des Corbieres
Sil Platz
Via Pianazzo, 5

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

SplitAddresslineHousenumber_CF ( AddressLineWithNumber ; ResultStreetNumberFormat )

Comments

Support this website.

Support this website.