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:backhand_index_pointing_down::backhand_index_pointing_down:

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;

 PIN......676305 ABU TALHA , GRAM TOEAN [OP] TOWN SARAI MEER SARAIMEER AZAMGARH PHULPUR , AZAMGARH , Uttar Pradesh, India, 9721994650, 9936718425, India 676305 THEEPA , DOOR NO 322 ,, KANCHAMALAI , PUSHPAVANAM POST , VEDARANIAM TK ,, PIN 614 809, NAGAPATTINAM , Tamil Nadu, India, 9786654543, 9786276228, India 614809 ANAS K , KAKKAT HOUSE ,ANANTHAVOOR POST , PUTHANATHANI , PIN 6763 01, MALAPPURAM , Kerala, India, 8606474470, 9747789474, India 676301 FAISAL AZAM KHAN , PARSAULI VILL , RUDAULI POST , PIN 2 2 4120, AYODIA , Uttar Pradesh, India, 8528414383, 9118043121, India 224120 MEEKALA SHEKA, W/O MEEKALA KHADEER BASHA , T SAKIBANDA POST , CHINNAMANDAM , RAYACHOTY - 5 1 6 214, KADAPA, Andhra Pradesh, India, 9000891262, 9949278304, India 516214 AHEEDA , JINE HOUSE , ARANGAD , KHANHANGAD POST , PIN 671 315, KASRAGODE , Kerala, India, 9961711715, 9946494369, India 671315 SAKKEER HUSSAIN M , KALLORA HOUSE PO OLAVANNA KAIMPALAM KOZHIKODE, Kerala, India, 6238711532, 8129644764, India VISHNU PRIYA SIVA SADANAM, ENNAKKADU PO MANNAR VIA MAVELIKKARA .. ALAPUZHA, Kerala, India, 9744902077, 04792466539, India ANIL KUMAR, VILL BAIRAMPUR BARWAN AKBARPUR PIN 2241 2 2, AMBEDKAR NAGAR, Uttar Pradesh, India, 8795184969, 9648849658, India 224122 NESAR AHMAD, AT BASDILA TOLA MURGIYA PO BASDILA KHAS PIN 8 4 142 8, GOPALGANJ, Bihar, India, 9199231459, 7079783479, India 841428

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?