All Versions Formulas and Functions

Copper Contributor

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

22 Replies

@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?

@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

@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?

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

@Gopal_Gope 

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?

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

@Gopal_Gope 

I give up.

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.

 

NameAddressCityStateCountry 1ID 1ID 2Country 2
PIN……676305 ABU TALHAGRAM TOEAN [OP] TOWN SARAI MEER SARAIMEER AZAMGARH PHULPURAZAMGARH Uttar PradeshIndia97219946509936718425India
PRAVEEN KUMAR KS/O B BALAN NAIR KARUVINCHAYIAM HOUSE KUTTIKOL PO .CHENGALA [VIA] PIN 671 541KASARAGODKeralaIndia81369206549539813011India
PRAVEEN KUMAR KS/O B BALAN NAIR KARUVINCHAYIAM HOUSE KUTTIKOL PO .CHENGALA [VIA] PIN 6715 41KASARAGODKeralaIndia81369206549539813011India
BALKEESHPEGAM NCHIRAHAMMADC/O NASEERAHAMMAD , NO – 2/477 , THATTAN KULAM STREET , UDAYAMARTHANDAPURAMTHIRUVARURTamil NaduIndia96883537706385508410India

 

@Detlef_Lewin @HansVogelaar 

See this for example:backhand_index_pointing_down::backhand_index_pointing_down:

 

IMG_20230825_083027.jpg

 

@Gopal_Gope 

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

 

@Gopal_Gope 

This is "dirty" data.

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

techcommunity_3909266.PNG

@Gopal_Gope 

 

How about regular expression? 

 

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

 

Screenshot_2023-08-25-12-19-19-479_cn.uujian.browser.jpg

 

@peiyezhu @Detlef_Lewin @HansVogelaar 

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

 

@Gopal_Gope 

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;

 

ADDRESS PIN

PIN......676305 ABU TALHA , GRAM TOEAN [OP] TOWN SARAI MEER SARAIMEER AZAMGARH PHULPUR , AZAMGARH , Uttar Pradesh, India, 9721994650, 9936718425, India676305
THEEPA , DOOR NO 322 ,, KANCHAMALAI , PUSHPAVANAM POST , VEDARANIAM TK ,, PIN 614 809, NAGAPATTINAM , Tamil Nadu, India, 9786654543, 9786276228, India614809
ANAS K , KAKKAT HOUSE ,ANANTHAVOOR POST , PUTHANATHANI , PIN 6763 01, MALAPPURAM , Kerala, India, 8606474470, 9747789474, India676301
FAISAL AZAM KHAN , PARSAULI VILL , RUDAULI POST , PIN 2 2 4120, AYODIA , Uttar Pradesh, India, 8528414383, 9118043121, India224120
MEEKALA SHEKA, W/O MEEKALA KHADEER BASHA , T SAKIBANDA POST , CHINNAMANDAM , RAYACHOTY - 5 1 6 214, KADAPA, Andhra Pradesh, India, 9000891262, 9949278304, India516214
AHEEDA , JINE HOUSE , ARANGAD , KHANHANGAD POST , PIN 671 315, KASRAGODE , Kerala, India, 9961711715, 9946494369, India671315
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, India224122
NESAR AHMAD, AT BASDILA TOLA MURGIYA PO BASDILA KHAS PIN 8 4 142 8, GOPALGANJ, Bihar, India, 9199231459, 7079783479, India841428

@peiyezhu  

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

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

@peiyezhu @Detlef_Lewin @HansVogelaar 

Hi,

Please help us

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

@Gopal_Gope 

Would a custom VBA function be OK?