search specific data and paste in side columns

sahil19z

New Member
Joined
Feb 3, 2014
Messages
1
i have these and many data in column A


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

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

<tbody>
</tbody>


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

Board Regular
Joined
Feb 1, 2014
Messages
66
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})))
 
Last edited:

Forum statistics

Threads
1,085,988
Messages
5,387,125
Members
402,045
Latest member
Hidalgo

Some videos you may like

This Week's Hot Topics

Top