search specific data and paste in side columns

[sahil19z]

i have these and many data in column A


[TABLE="width: 215"]
<tbody>[TR]
[TD]101 Cicilia Sagar Apt, J B Nagar, ANdheri East, MUmbai 400059[/TD]
[/TR]
[TR]
[TD]Panch Leela, Flat no 201, Powai Vihar, Hiranandani, Mumbai 400076[/TD]
[/TR]
[TR]
[TD]B108 ,Daffodil Bdg,Dosti Acres Mumbai[/TD]
[/TR]
[TR]
[TD]402, Chhtrapati Apt, Pawarnagar, Thane (W), 400610[/TD]
[/TR]
[TR]
[TD]Chembur Mumbai MAHARASHTRA 400071 India[/TD]
[/TR]
[TR]
[TD]Flat No. 1, Sai Villa, Sai Baba Complex,Goregaon(East), Mumbai, Maharashtra[/TD]
[/TR]
[TR]
[TD]C/O JAYANTA CHATTOPADHYAYA, FLAT NO-20/C 502, MHADA,NTPC MCOMPLEX,A.S.MARG ROAD,POWAI,MUMBAI-76.[/TD]
[/TR]
[TR]
[TD]9B 1302 Powai Cosmopolitan Housing Society Rambaug, Powai, City:Mumbai, Pin: 400076[/TD]
[/TR]
[TR]
[TD]Lobo Cottage, Dr Peter Diaz Road,Bandra(W),Mumbai-50[/TD]
[/TR]
[TR]
[TD]303A, Pragati appt., Pakhadi, Kharegaov, Kalwa (W) Thane[/TD]
[/TR]
[TR]
[TD]Address: A-1,503 Lokmilan complex, Chandivali farm road, Chandivali, Andheri East Mumbai - 400072[/TD]
[/TR]
[TR]
[TD]c-1904,Agarwal trinity towers,D'monte lane, Orlem , Malad (west) Mumbai -400064[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Flat No 704, A Wing, Navjyotirling Apartment,Near Riddhi Garden,Malad-400092[/TD]
[/TR]
[TR]
[TD]G 104 bhumi park malad west mumbai 400096[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 215"]
<tbody>[TR]
[TD]101 Cicilia Sagar Apt, J B Nagar, ANdheri East, MUmbai 400059[/TD]
[/TR]
[TR]
[TD]Panch Leela, Flat no 201, Powai Vihar, Hiranandani, Mumbai 400076[/TD]
[/TR]
[TR]
[TD]B108 ,Daffodil Bdg,Dosti Acres Mumbai[/TD]
[/TR]
[TR]
[TD]402, Chhtrapati Apt, Pawarnagar, Thane (W), 400610[/TD]
[/TR]
[TR]
[TD]Chembur Mumbai MAHARASHTRA 400071 India[/TD]
[/TR]
[TR]
[TD]Flat No. 1, Sai Villa, Sai Baba Complex,Goregaon(East), Mumbai, Maharashtra[/TD]
[/TR]
[TR]
[TD]C/O JAYANTA CHATTOPADHYAYA, FLAT NO-20/C 502, MHADA,NTPC MCOMPLEX,A.S.MARG ROAD,POWAI,MUMBAI-76.[/TD]
[/TR]
[TR]
[TD]9B 1302 Powai Cosmopolitan Housing Society Rambaug, Powai, City:Mumbai, Pin: 400076[/TD]
[/TR]
[TR]
[TD]Lobo Cottage, Dr Peter Diaz Road,Bandra(W),Mumbai-50[/TD]
[/TR]
[TR]
[TD]303A, Pragati appt., Pakhadi, Kharegaov, Kalwa (W) Thane[/TD]
[/TR]
[TR]
[TD]Address: A-1,503 Lokmilan complex, Chandivali farm road, Chandivali, Andheri East Mumbai - 400072[/TD]
[/TR]
[TR]
[TD]c-1904,Agarwal trinity towers,D'monte lane, Orlem , Malad (west) Mumbai -400064[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Flat No 704, A Wing, Navjyotirling Apartment,Near Riddhi Garden,Malad-400092[/TD]
[/TR]
[TR]
[TD]G 104 bhumi park malad west mumbai 400096[/TD]
[/TR]
</tbody>[/TABLE]


i want to simply extract pincode given here like 400096, 400064 in a cell and paste it side column
somewhere data is like in these form as well.

m - 51
m:51
pin - 51
pin : 51
pin:51

now i want to find these uncommon format of data and put it in side column as 400051

can i do that by any means possible.
through macro or by formula.

Thank you

 

[KrisSquare]

Try this:

I picked up this from another post of this site and adapted it to your situation. It would work with pincodes of any length (1 to 6 digits) which are at the end of the string

Thanks to pgc01

Use the below formula in a new column of your sheet


=TRIM(LOOKUP(999999,--RIGHT(TRIM(RIGHT(TRIM(A2),15)),{1,2,3,4,5,6})))

 

[azumi]

Assumed your data in Cell A2 and down, put this in Cell B2 and copied down as necessary, just try it:

=IF(A2="","",IFERROR(VALUE("4000"&TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2,"-"," "),"."," "),3))),""))

https://www.dropbox.com/s/rfgj29vo236p7b3/sahil19z.xlsx

or see the file, hope it works