# All Versions Formulas and Functions

Copper Contributor

# 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

22 Replies

# Re: All Versions Formulas and Functions

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?

# Re: All Versions Formulas and Functions

@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

# Re: All Versions Formulas and Functions

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?

# Re: All Versions Formulas and Functions

Hi,
we are not talking about in pin them we have to discuss about India Post office pin code number request your support in identifying a formula which will 6 digit capture pin code with spaces and mobile number pin code not with spaces irrespective wherever they are in the address column

What are you asking us, we are unable to understand your question???

Thanks

# Re: All Versions Formulas and Functions

In the first example, the PIN is 676305

In the second example, it is 671 541 (or 671541 if you prefer)

What should the formula return for the 4th example?

# Re: All Versions Formulas and Functions

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

I give up.

# Re: All Versions Formulas and Functions

I believe the data should look like this. But I can't see the connection with "pin". It's in the Name column, and then in the Address column and then not in any column.

 Name Address City State Country 1 ID 1 ID 2 Country 2 PIN……676305 ABU TALHA GRAM TOEAN [OP] TOWN SARAI MEER SARAIMEER AZAMGARH PHULPUR AZAMGARH Uttar Pradesh India 9721994650 9936718425 India PRAVEEN KUMAR K S/O B BALAN NAIR KARUVINCHAYIAM HOUSE KUTTIKOL PO .CHENGALA [VIA] PIN 671 541 KASARAGOD Kerala India 8136920654 9539813011 India PRAVEEN KUMAR K S/O B BALAN NAIR KARUVINCHAYIAM HOUSE KUTTIKOL PO .CHENGALA [VIA] PIN 6715 41 KASARAGOD Kerala India 8136920654 9539813011 India BALKEESHPEGAM NCHIRAHAMMAD C/O NASEERAHAMMAD , NO – 2/477 , THATTAN KULAM STREET , UDAYAMARTHANDAPURAM THIRUVARUR Tamil Nadu India 9688353770 6385508410 India

# Re: All Versions Formulas and Functions

See this for example👇👇

# Re: All Versions Formulas and Functions

Please provide a sample file. Nobody wants to re-type the data.

# Re: All Versions Formulas and Functions

This is "dirty" data.

If I try TEXTSPLIT(E2,",") the fields don't line up.

# Re: All Versions Formulas and Functions

Hi,

Apply the formula which I have shared with you in that file so that you can understand better and the result which is coming out, take screenshot and share with me

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

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

My microsoft Office Excel 2007

# Re: All Versions Formulas and Functions

if possible,thr this online tool.

http://e.anyoupin.cn/EData/?s=Extract_Pin

//select *,replace(regexp2('(?<=PIN)(?:\D+)?([\d\s]+)',ADDRESS,1),' ','') PIN from Extract_Pin_code;
select *,replace(regexp2('(?:\D+)((?:\d\s?){6})(?=\D)',ADDRESS,1),' ','') PIN from Extract_Pin_code;

# Re: All Versions Formulas and Functions

Hi,

Provided array formula was really helpful and more importantly “IT’S WORK”. Thank you very much…appreciate your prompt response in resolving the issue….

We fail to use this tool because we can't understand it language

To improve the help please share us the file by applying this formula inside excel file

# Re: All Versions Formulas and Functions

Sorry.I have no idea how to solve your question with Excel formula.

I have recorded a video about how to parse your PIN Code with the online tool.
I guess it is easy because you just copy the column Address and paste to the text box then submit to run it.
Here is the video.
https://b23.tv/1qWlL9u

# Re: All Versions Formulas and Functions

Hi,

I had come with great hope that a solution for all versions would be found here

Is there no solution to solve this problem?

I need excel formula my microsoft Office Excel 2007

# Re: All Versions Formulas and Functions

Would a custom VBA function be OK?