Forum Discussion
All Versions Formulas and Functions
You may download the file from here
How about regular expression?
select *,regexp2('(?<=PIN)(?:\D+)?([\d\s]+)',ADDRESS,1) PIN from Extract_Pin_code;
- =WEBSERVICE("http://e.anyoupin.cn/eh3/?preg_match~(?:\D+)((?:\d\s?){6})(?=\D)~" & A2)
o365 python
https://www.w3schools.com/python/python_regex.asp
vba
https://www.tutorialandexample.com/vba-regex#:~:text=The%20steps%20to%20create%20Regex%20in%20VBA%20are,select%20the%20%E2%80%9CMicrosoft%20VBScript%20Regular%20Expression%205.5%E2%80%9D%20option. - Dear community I'm sorry for that
It was helpful for us the array formula was really helpful and more importantly“it’s work” but can we improve it further?
formula- =INDEX(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".","")," ",""),"-",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".","")," ",""),"-","")))),6),MATCH(TRUE,LEN(1*(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".","")," ",""),"-",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".","")," ",""),"-","")))),6)))=6,0),1)
If an address does not have a 6-digit pin number, the formula should return a blank value for that column. Why did you remove your reply to my question about a VBA solution?
Would a custom VBA function be OK?
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
- 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 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
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 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
