Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 |);
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
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?
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 |);
Modified that above code. Please check again
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?
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 |);
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
try
= 'HJ'&SubField(yourText,'HJ',2)
hth
Sasi
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 |);