Table Formula Throws VALUE Error at Bottom of Column

[NorthbyNorthwest]

Hi, everyone. I have a formula copied down a column "DC", rows 6 - 89. Formula works until row 84 when a VALUE error occurs. The formula is simple. It's in column "DC" and references the cell to the immediate left, in column "DB."" Why would a formula that worked for 70+ rows suddenly throw an error? Attached is a pic of the table.

The formula in column "DC" is =IF($DB6="","",SUBSTITUTE(SUBSTITUTE(LEFT($DB6,SEARCH("]",$DB6)),"[",""),"]",""))

Copied down the errors start in row 84, =IF($DB84="","",SUBSTITUTE(SUBSTITUTE(LEFT($DB84,SEARCH("]",$DB84)),"[",""),"]",""))

There is also a formula copied down column "DB". It is =IFERROR(IF([@Employee]="","",CONCAT(tblDetails[@[Err1]:[Err23]],tblDetails[@[Err30]:[Err46]])),""). If I remove this formula from a cell in column "DB" and replace with a real value, then the formula in "DC" works. So, is there a problem with the formula in column "DB"? Is yes, what is it?

Any assistance would be appreciated. Thanks in advance.

 

[StephenCrump]

Have a close look at what you've got in cell DB84. It's not blank, otherwise your formula would have returned "".

Is it perhaps one or more space characters? To test, try the formula: =LEN(DB84) in another cell.

If that is the problem, you could test: =IF(TRIM($DB84)="","",SUBSTITUTE ...

 

[StephenCrump]

By the way, if you are only going to use Excel365, you could use the TEXTBEFORE and TEXTAFTER functions instead.

But it's not clear what column DB might contain? If it's just one error value wrapped in [ ], you could just strip the first and last characters.

Are multiple errors concatenated, e.g. [1111][2222] a possibility?

 

[Peter_SSs]

.. or tidy up the space(s) in column DB with
=IFERROR(IF([@Employee]="","",TRIM(CONCAT(tblDetails[@[Err1]:[Err23]],tblDetails[@[Err30]:[Err46]]))),"")

 

[NorthbyNorthwest]

Have a close look at what you've got in cell DB84. It's not blank, otherwise your formula would have returned "".

Is it perhaps one or more space characters? To test, try the formula: =LEN(DB84) in another cell.

If that is the problem, you could test: =IF(TRIM($DB84)="","",SUBSTITUTE ...

Thanks Stephen. You were right. LEN(DB84) = 1. So I went back to the cells referenced in the CONCAT formula ((tblDetails[@[Err1]:[Err23]],tblDetails[@[Err30]:[Err46]])) and cleared the cells. The LEN formula then returned a zero.

 

[NorthbyNorthwest]

By the way, if you are only going to use Excel365, you could use the TEXTBEFORE and TEXTAFTER functions instead.

But it's not clear what column DB might contain? If it's just one error value wrapped in [ ], you could just strip the first and last characters.

Are multiple errors concatenated, e.g. [1111][2222] a possibility?

Yes, Stephen. The formula is concatenating a series of numbers encased by brackets. Then I have to break out each number into an individual cell minus the brackets. So, if DB84 formula returned
[309][320][1404][1601][2000][2002] I have formulas to break out numbers. In this example formulas are:

DC84 =IF(DB84="","",SUBSTITUTE(SUBSTITUTE(LEFT(DB84,SEARCH("]",DB84)),"[",""),"]",""))


DD84 =IF(DB84="","",SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE(TRIM(DB84),"]",REPT("]",LEN(DB84))), 1*LEN(DB84)+1, LEN(DB84))),"]",""),"[",""))


DE84 =IF(DB84="","",SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE(TRIM(DB84),"]",REPT("]",LEN(DB84))), 2*LEN(DB84)+1, LEN(DB84))),"]",""),"[",""))


DF84 =IF(DB84="","",SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE(TRIM(DB84),"]",REPT("]",LEN(DB84))), 3*LEN(DB84)+1, LEN(DB84))),"]",""),"[",""))


DG84 =IF(DB84="","",SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE(TRIM(DB84),"]",REPT("]",LEN(DB84))), 4*LEN(DB84)+1, LEN(DB84))),"]",""),"[",""))


DH84 =IF(DB84="","",SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE(TRIM(DB84),"]",REPT("]",LEN(DB84))), 5*LEN(DB84)+1, LEN(DB84))),"]",""),"[",""))

If you have a better, more efficient way to extract the numbers out, I'd appreciate it.

 

[NorthbyNorthwest]

.. or tidy up the space(s) in column DB with
=IFERROR(IF([@Employee]="","",TRIM(CONCAT(tblDetails[@[Err1]:[Err23]],tblDetails[@[Err30]:[Err46]]))),"")

Thanks Peter. I took your suggestion and amended the formula.

 

[Peter_SSs]

You're welcome. Glad to contribute.

If you have a better, more efficient way to extract the numbers out, I'd appreciate it.

What about like this?

NorthbyNorthwest.xlsm
	DB	DC	DD	DE
5	Errors	Code1	Code2	Code3
6	[1105][2222][1238][3214]	1105	2222	1238
7
8	[2222]	2222
Sheet1

Cell Formulas
Range	Formula
DB6:DB8	DB6	=IFERROR(IF([@Employee]="","",TRIM(CONCAT(tblDetails[@[Err1]:[Err23]],tblDetails[@[Err30]:[Err46]]))),"")
DC6:DC8	DC6	=IFERROR(INDEX(TEXTSPLIT([@Errors],{"[","]"}),2),"")
DD6:DD8	DD6	=IFERROR(INDEX(TEXTSPLIT([@Errors],{"[","]"}),4),"")
DE6:DE8	DE6	=IFERROR(INDEX(TEXTSPLIT([@Errors],{"[","]"}),6),"")

 

[NorthbyNorthwest]

You're welcome. Glad to contribute.


What about like this?

NorthbyNorthwest.xlsm
	DB	DC	DD	DE
5	Errors	Code1	Code2	Code3
6	[1105][2222][1238][3214]	1105	2222	1238
7
8	[2222]	2222
Sheet1

Cell Formulas
Range	Formula
DB6:DB8	DB6	=IFERROR(IF([@Employee]="","",TRIM(CONCAT(tblDetails[@[Err1]:[Err23]],tblDetails[@[Err30]:[Err46]]))),"")
DC6:DC8	DC6	=IFERROR(INDEX(TEXTSPLIT([@Errors],{"[","]"}),2),"")
DD6:DD8	DD6	=IFERROR(INDEX(TEXTSPLIT([@Errors],{"[","]"}),4),"")
DE6:DE8	DE6	=IFERROR(INDEX(TEXTSPLIT([@Errors],{"[","]"}),6),"")

Thanks again, Stephen. This forum has been a lifesaver for me. You guys really rock! All the Best.

 

[NorthbyNorthwest]

You're welcome. Glad to contribute.


What about like this?

NorthbyNorthwest.xlsm
	DB	DC	DD	DE
5	Errors	Code1	Code2	Code3
6	[1105][2222][1238][3214]	1105	2222	1238
7
8	[2222]	2222
Sheet1

Cell Formulas
Range	Formula
DB6:DB8	DB6	=IFERROR(IF([@Employee]="","",TRIM(CONCAT(tblDetails[@[Err1]:[Err23]],tblDetails[@[Err30]:[Err46]]))),"")
DC6:DC8	DC6	=IFERROR(INDEX(TEXTSPLIT([@Errors],{"[","]"}),2),"")
DD6:DD8	DD6	=IFERROR(INDEX(TEXTSPLIT([@Errors],{"[","]"}),4),"")
DE6:DE8	DE6	=IFERROR(INDEX(TEXTSPLIT([@Errors],{"[","]"}),6),"")

Thanks again, Stephen. This forum has been a lifesaver for me. You guys really rock! All the Best.