Forum Discussion

Gopal_Gope's avatar
Gopal_Gope
Copper Contributor
Aug 23, 2023

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

  • Gopal_Gope 

    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_Gope's avatar
      Gopal_Gope
      Copper 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 understand

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        Gopal_Gope 

        Your formula doesn't work at all, since it contains "curly" quotes instead of straight quotes.

        But what should the PIN in example 4 be?

Resources