Extract 6 digits from address

[prashbhosale]

Hello experts

I have to extract 6 digit Indian postal code from address field from an excel file.

That address field has values like below

Flat 101 Kharadi Pune 411014 India

So I need continuous 6 digit number to be extracted as my Postal code. When I use keepchar for numbers it brings 9 digit number or sometimes doesn't bring anything for some unknown reasons. 

Thanks in advance for the help. 

[maxgro · Accepted solution]

If the 6 digits word is always in the same position (second word starting at the end of the field)

tmp:
load recno() as ID, Address inline [
Address
Flat 101 Kharadi Pune 411014 India
Flat 101 Kharadi Pune 123456 India
Flat 101 Kharadi Pune 098765 India
aaaa 12345 yyyy zzzz 000000 India
];

tmp3:
load ID, subfield(Address, ' ', -2) as Field3
resident tmp;

Another idea, split the field and check for the lenght

tmp:
load recno() as ID, Address inline [
Address
Flat 101 Kharadi Pune 411014 India
Flat 101 Kharadi Pune 123456 India
Flat 101 Kharadi Pune 098765 India
aaaa 12345 yyyy zzzz 000000 India
aaaa 12345 yyyy zzzz India
c 123456 c c c ndia
d 123456 d d d d d d d d India
];

tmp2:
load * where len(Field2) = 6;      // check lenght
LOAD
ID,
subfield(Address, ' ') as Field2     //split
Resident tmp;

[prashbhosale]

Thank you Maxgro