Forum Discussion
Excel formula help
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.
Hi Gopal_Gope,
To improve the formula and make it return a blank value when there is no 6-digit PIN number in the address, you can modify it as follows:=IFERROR(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),""),"")
The changed formula should return a blank ("") if there is an error in the formula. This way, if there is no 6-digit PIN number in the address, the formula will return a blank value instead of an error.Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic
4 Replies
Hi Gopal_Gope,
To improve the formula and make it return a blank value when there is no 6-digit PIN number in the address, you can modify it as follows:=IFERROR(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),""),"")
The changed formula should return a blank ("") if there is an error in the formula. This way, if there is no 6-digit PIN number in the address, the formula will return a blank value instead of an error.Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic
- Hi,
Will this work for a solution for all versions
My microsoft Office Excel 2007
Please help and request your supportHi Gopal_Gope,
thanks for the update.
I don't have an opportunity to test the formula on older versions but the formula should work in Excel 2007, only issue that can occur is that Excel 2007 has formula length limitations, so very long addresses could be problematic.Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic
