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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top