Forum Discussion
All Versions Formulas and Functions
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
Examples 1), 2) and 3) contain the word PIN. This might help us to find the pin code.
But example 4) does not contain the word PIN. What is the pin code in this example?
- Gopal_GopeCopper Contributor
HansVogelaar
Hi,
The first one is right he what the formula should return here is PIN is 676305
The second one is right he what the formula should return here is PIN is 671541 if you prefer
The four is right he what the formula should return here is blank
blank should come because there is no 6 digit number in this column
I hope you have understandYour formula doesn't work at all, since it contains "curly" quotes instead of straight quotes.
But what should the PIN in example 4 be?