Forum Discussion

Copper Contributor

Sep 07, 2023

Solved

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,".","")," ",""),"...

community

LeonPavesic
Sep 11, 2023
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

Gopal_Gope

Copper Contributor

Sep 13, 2023

Hi,
Will this work for a solution for all versions

My microsoft Office Excel 2007

Please help and request your support

LeonPavesic

Silver Contributor

Sep 13, 2023

Hi 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

Gopal_Gope
Copper Contributor
Sep 15, 2023
LeonPavesic
subject about "Issue with Formula in Excel Sheet: Requesting Help"
the formula is not working as and me getting blank values in the column
Your given formula
=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),""),"")