Forum Discussion
Gopal_Gope
Aug 23, 2023Copper 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 num...
Gopal_Gope
Aug 25, 2023Copper Contributor
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
peiyezhu
Aug 25, 2023Bronze Contributor
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
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
- peiyezhuSep 07, 2023Bronze Contributor=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. - Gopal_GopeSep 07, 2023Copper ContributorDear 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. - HansVogelaarAug 25, 2023MVP
Would a custom VBA function be OK?
- Gopal_GopeAug 25, 2023Copper Contributor
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