All Versions Formulas and Functions

[Gopal_Gope]

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

[#2]

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

[#3]

@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

[#4]

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

[#5]

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

[#6]

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

[#7]

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

[#8]

@Gopal_Gope 

I give up.

[#9]

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

[#10]

@Detlef_Lewin @HansVogelaar 

See this for example👇👇

[#11]

@Gopal_Gope 

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

[#12]

@Detlef_Lewin  @HansVogelaar 

You may download the file from here

https://1drv.ms/x/s!Al_W0BkwmMJViWPXh0jA8xoy2jye  

[#13]

@Gopal_Gope 

This is "dirty" data.

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

[#14]

@Gopal_Gope 

How about regular expression? 

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

[#15]

@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

[#16]

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

[#17]

@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

[#18]

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

[#19]

@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

[#20]

@Gopal_Gope 

Would a custom VBA function be OK?