How t separate postcode from complete address field?

[#1]

Hi,

I have address field as below in table

4 Mountview, Keady, wards HJ60 3RG

34 Wyndell Heights, wards HJ23 7GX

234 Bawnmore Park, wards HJ36 7BQ

56 Thornbrooke, Ahoghill, Toristreet, HJ42 1PZ

Woodlands, 2 Bachelors, Portadown, wards HJ63 5BQ

90 Leven Park, Huyyijane, HJ5 7JA

98 Dunsuivnish Avenue, wards HJ55 7EP

5e Castle Hill, Toristreet, HJ70 1JP

23 Dunsuivnish Avenue, Toristreet, HJ55 7EP

78 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

12 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

2 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

11 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

67 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

43 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

45 Ballybeen Park, Dundonald, Huyyijane, HJ16 2QB

90 Abbey Ring, Holywood, HJ18 9NS

23 Abbey Ring, Holywood, HJ18 9NS

I need to separate Postcdoe from complete address. Always postcode starts with 'HJ'. how to separate postcode from entire address for each address value.

Please can anyone suggest me how to do this.

Thanks.

[sunny_talwar]

May be this:

SubField(Address, ' ', -2) as PostCode

Left(Address, Len(Address) - 10) as Address

SubField(Address, ' ', -2) & ' ' & SubField(Address, ' ', -1) as PostCode,

Left(Address, Len(Address) - 9) as Address

[#3]

Thanks. when I used this it is showing only first half part of postcode

for eg: the complete postcode in below address is HJ60 3RG

4 Mountview, Keady, wards HJ60 3RG

but it is showing only HJ60

do I need to modify anything here?

[sunny_talwar]

Script:

Table:

LOAD SubField(Address, ' ', -2) & ' ' & SubField(Address, ' ', -1) as PostCode,

  Left(Address, Len(Address) - 9) as Address;

LOAD * Inline [

Address

4 Mountview, Keady, wards HJ60 3RG

34 Wyndell Heights, wards HJ23 7GX

234 Bawnmore Park, wards HJ36 7BQ

56 Thornbrooke, Ahoghill, Toristreet, HJ42 1PZ

Woodlands, 2 Bachelors, Portadown, wards HJ63 5BQ

90 Leven Park, Huyyijane, HJ5 7JA

98 Dunsuivnish Avenue, wards HJ55 7EP

5e Castle Hill, Toristreet, HJ70 1JP

23 Dunsuivnish Avenue, Toristreet, HJ55 7EP

78 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

12 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

2 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

11 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

67 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

43 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

45 Ballybeen Park, Dundonald, Huyyijane, HJ16 2QB

90 Abbey Ring, Holywood, HJ18 9NS

23 Abbey Ring, Holywood, HJ18 9NS

] (delimiter is |);

[sunny_talwar]

Modified that above code. Please check again

[#6]

Great Thanks. just checked your modify script. for some of the address there is no postcode like below

109 Ashgrove Manor Killpo Donegal

in the Postcode filed for above address it is showing as Killpo Donegal

how to change the script to not enter into Postcode field when there is no postcod ein complete address? so I need the Postcode column should show blank when there is no Postcode.

would it be possible?

[sunny_talwar]

Check this:

Table:

LOAD If(Left(SubField(Address, ' ', -2), 2) = 'HJ', SubField(Address, ' ', -2) & ' ' & SubField(Address, ' ', -1)) as PostCode,

  If(Left(SubField(Address, ' ', -2), 2) = 'HJ', Left(Address, Len(Address) - 9), Address) as Address;

LOAD * Inline [

Address

4 Mountview, Keady, wards HJ60 3RG

34 Wyndell Heights, wards HJ23 7GX

234 Bawnmore Park, wards HJ36 7BQ

56 Thornbrooke, Ahoghill, Toristreet, HJ42 1PZ

Woodlands, 2 Bachelors, Portadown, wards HJ63 5BQ

90 Leven Park, Huyyijane, HJ5 7JA

98 Dunsuivnish Avenue, wards HJ55 7EP

5e Castle Hill, Toristreet, HJ70 1JP

23 Dunsuivnish Avenue, Toristreet, HJ55 7EP

78 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

12 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

2 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

11 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

67 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

43 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

45 Ballybeen Park, Dundonald, Huyyijane, HJ16 2QB

90 Abbey Ring, Holywood, HJ18 9NS

23 Abbey Ring, Holywood, HJ18 9NS

109 Ashgrove Manor Killpo Donegal

] (delimiter is |);

[#8]

Excellent. Thanks. one last request please when there is gap in between Postcode like below how to get these type of Postcodes into Postcode column

90 Railway Court, Dungiven, Yo Janerry HJ 34 4IU

[sasiparupudi1]

try

= 'HJ'&SubField(yourText,'HJ',2)

hth

Sasi

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD If(Left(SubField(Address, ' ', -2), 2) = 'HJ', SubField(Address, ' ', -2) & ' ' & SubField(Address, ' ', -1),

  If(Left(SubField(Address, ' ', -3), 2) = 'HJ', SubField(Address, ' ', -3) & ' ' & SubField(Address, ' ', -2) & ' ' & SubField(Address, ' ', -1))) as PostCode,

  If(Left(SubField(Address, ' ', -2), 2) = 'HJ', Left(Address, Len(Address) - 9),

  If(Left(SubField(Address, ' ', -3), 2) = 'HJ', Left(Address, Len(Address) - 10), Address)) as Address;

LOAD * Inline [

Address

4 Mountview, Keady, wards HJ60 3RG

34 Wyndell Heights, wards HJ23 7GX

234 Bawnmore Park, wards HJ36 7BQ

56 Thornbrooke, Ahoghill, Toristreet, HJ42 1PZ

Woodlands, 2 Bachelors, Portadown, wards HJ63 5BQ

90 Leven Park, Huyyijane, HJ5 7JA

98 Dunsuivnish Avenue, wards HJ55 7EP

5e Castle Hill, Toristreet, HJ70 1JP

23 Dunsuivnish Avenue, Toristreet, HJ55 7EP

78 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

12 Flax Valley, Irvinestown, Enniskillen, HJ94 1FL

2 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

11 Newtown Road, Camlough, Huyyijane, HJ35 7JJ

67 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

43 St Oliver Plunkett Park, Camlough, Huyyijane, HJ35 7JE

45 Ballybeen Park, Dundonald, Huyyijane, HJ16 2QB

90 Abbey Ring, Holywood, HJ18 9NS

23 Abbey Ring, Holywood, HJ18 9NS

109 Ashgrove Manor Killpo Donegal

90 Railway Court, Dungiven, Yo Janerry HJ 34 4IU

] (delimiter is |);