All Versions Formulas and Functions

[Gopal_Gope]

Hi,

Request your support in identifying a formula which i used to extract Pin code

• formula used by me is only capturing the pincode, however, it fails to capture pincode with spaces and mobile number

My microsoft Office Excel 2007

Formula— =MID(E6,FIND(“@@@@@@”,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E6,1,”@”),2,”@”),3,”@”),4,”@”),5,”@”),6,”@”),7,”@”),8,”@”),9,”@”),0,”@”)),6)*1

The Demo Data

1) PIN……676305 ABU TALHA , GRAM TOEAN [OP] TOWN SARAI MEER SARAIMEER AZAMGARH PHULPUR ……, AZAMGARH , Uttar Pradesh, India, 9721994650, 9936718425, India

this can be extracted by the above formula not able to extract the below pincode with spaces mobile number

2) PRAVEEN KUMAR K, S/O B BALAN NAIR KARUVINCHAYIAM HOUSE KUTTIKOL PO .CHENGALA [VIA] PIN 671 541, KASARAGOD, Kerala, India, 8136920654, 9539813011, India

3) PRAVEEN KUMAR K, S/O B BALAN NAIR KARUVINCHAYIAM HOUSE KUTTIKOL PO .CHENGALA [VIA] PIN 6715 41, KASARAGOD, Kerala, India, 8136920654, 9539813011, India

4) BALKEESHPEGAM NCHIRAHAMMAD, C/O NASEERAHAMMAD , NO – 2/477 , THATTAN KULAM STREET , UDAYAMARTHANDAPURAM , THIRUVARUR, Tamil Nadu, India, 9688353770, 6385508410, India

give me a solution for all versions

Please help and request your support

[#2]

@Gopal_Gope 

Why did you remove your reply to my question about a VBA solution?

[#3]

Dear community I'm sorry for that

It was helpful for us the array formula was really helpful and more importantly“it’s work” but can we improve it further?

formula- =INDEX(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".","")," ",""),"-",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".","")," ",""),"-","")))),6),MATCH(TRUE,LEN(1*(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".","")," ",""),"-",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".","")," ",""),"-","")))),6)))=6,0),1)
If an address does not have a 6-digit pin number, the formula should return a blank value for that column.

[#4]

=WEBSERVICE("http://e.anyoupin.cn/eh3/?preg_match~(?:\D+)((?:\d\s?){6})(?=\D)~" & A2)


o365 python
https://www.w3schools.com/python/python_regex.asp

vba
https://www.tutorialandexample.com/vba-regex#:~:text=The%20steps%20to%20create%20Regex%20in%20VBA%20....