Forum Discussion
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...
Would a custom VBA function be OK?
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.
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.
- =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.
