Trim Address Functions

I have been working on a new database and have found the need to separate a combined text field into various columns, obviously you can do this using the Left and Right Trim functions but how do you trim a combined text field into parts that are not fixed width?

For this example I will use an address field txt_Address which contains addresses of in determinant length. To do this you need to split it up by each address field in this field these are identified by commas

First step therefore is to identify the commas as each comma needs to be placed on a different line.

(Len([txt_Address])-Len(Replace([txt_Address],",","")))/1 This will tell you how many comma’s there are.

As you can see some of the addresses do not have a comma, so when we write our first expression we need to qualify if there is a comma to split by and if not to just add the whole field

Address Line 1 =

IIf(((Len([txt_Address])-Len(Replace([txt_Address],",","")))/1)=0,[txt_Address], Left([txt_Address],InStr(1,[txt_Address],",")-1))

The Address Line 2 query is obviously different for each field depending on how many commas are present, start the expression again by checking how many commas present and if it is less than 1 return a blank field.

IIf(((Len([txt_Address])-Len(Replace([txt_Address],",","")))/1)=0,"",>

If there is a comma however then you need to trim from the first comma

Right(Trim([txt_Address]),Len(Trim([txt_Address]))-InStr(1,[txt_Address],",")

However if there is more than one comma you need to left trim the newly created field to just take the first line. You can do this by replacing the above right trim into the first left trim in place of the field names.

You get a very confusing looking Address Line 2 =

IIf(((Len([txt_Address])-Len(Replace([txt_Address],",","")))/1)=0,"",IIf(((Len([txt_Address])-Len(Replace([txt_Address],",","")))/1)=1,Right(Trim([txt_Address]),Len(Trim([txt_Address]))-InStr(1,[txt_Address],",")),Left(Right(Trim([txt_Address]),Len(Trim([txt_Address]))-InStr(1,[txt_Address],",")),InStr(1,Right(Trim([txt_Address]),Len(Trim([txt_Address]))-InStr(1,[txt_Address],",")),",")-1))) It looks very confusing but it works

If you wish to have more than three address lines then you simply need to keep shortening the trimmed value by one comma at a time until you can “left” the part that you want. For this one however I will place everything else onto a third line for simplicities sake.

This simply uses the above functions in place of the actual field name

Adress Line 3 =

IIf(((Len([txt_Address])-Len(Replace([txt_Address],",","")))/1)=1,"",right(trim(right(trim([txt_Address]),Len(Trim([txt_Address]))-InStr(1,[txt_Address],","))),Len(Trim(Right(Trim([txt_Address]),Len(Trim([txt_Address]))-InStr(1,[txt_Address],","))))-InStr(1,Right(Trim([txt_Address]),Len(Trim([txt_Address]))-InStr(1,[txt_Address],",")),",")))

So the final Query is as follows:

SELECT txt_Address, txt_PostCode, IIf(((Len([txt_Address])-Len(Replace([txt_Address],",","")))/1)=0,[txt_Address],Left([txt_Address],InStr(1,[txt_Address],",")-1)) AS [Address Line 1], IIf(((Len([txt_Address])-Len(Replace([txt_Address],",","")))/1)=0,"",IIf(((Len([txt_Address])-Len(Replace([txt_Address],",","")))/1)=1,Right(Trim([txt_Address]),Len(Trim([txt_Address]))-InStr(1,[txt_Address],",")),Left(Right(Trim([txt_Address]),Len(Trim([txt_Address]))-InStr(1,[txt_Address],",")),InStr(1,Right(Trim([txt_Address]),Len(Trim([txt_Address]))-InStr(1,[txt_Address],",")),",")-1))) AS [Address Line 2], IIf(((Len([txt_Address])-Len(Replace([txt_Address],",","")))/1)=1,"",right(trim(right(trim([txt_Address]),Len(Trim([txt_Address]))-InStr(1,[txt_Address],","))),Len(Trim(Right(Trim([txt_Address]),Len(Trim([txt_Address]))-InStr(1,[txt_Address],","))))-InStr(1,Right(Trim([txt_Address]),Len(Trim([txt_Address]))-InStr(1,[txt_Address],",")),","))) AS [Adress Line 3], (Len([txt_Address])-Len(Replace([txt_Address],",","")))/1 AS [Comma Count] FROM tbl_MainData_agb;

Maybe next time I will just insist that they place each line in a separate field.