Convert formula to its value for use in another formula

[est249]

I am trying to utilize the value in E2 (which is a formula) for another formula in D2 and it creates an error. The formula in E2 does a conversion for a sample ID code. The formula in D2 formats that value with the first four characters of that value and the last three digits from F2. Mini Sheet included that includes both formulas.

SampleBook.xlsx
	A	B	C	D	E	F	G	H
1		well #	Patient Name	Accession	Sample ID Code	accession	Resulted	Result-Date
2	1	A-1	ZZTEST, COV FIVE	ZCF-0051	ZCS-00	09-22-271-0051
3	2	A-2	ZZTEST, COV SIX		ZCS-00	09-22-271-0053
Sheet1

Cell Formulas
Range	Formula
D2	D2	=CONCATENATE(LEFT(E2,4),RIGHT(F2,4))
E2:E3	E2	=CONCATENATE(LEFT(C2)&IF(ISNUMBER(FIND(" ",C2)),MID(C2,FIND(" ",C2)+1,1),"")&IF(ISNUMBER(FIND(" ",C2,FIND(" ",C2)+1)),MID(C2,FIND(" ",C2,FIND(" ",C2)+1)+1,1),""),"-",RIGHT(YEAR(D2),2))

 

[Automatrix]

You're missing a length parameter after C2.

=CONCATENATE(LEFT(C2)

 

[jasonb75]

What are you trying to do with the RIGHT(YEAR(D2),2) part of the formula?

That is creating a circular reference which is the root of the problem, along with the fact that there is no date to extract a year from anyway.

 

[est249]

Thank you jasonb75.

I updated the formula in E2 to the below and that worked! Much appreciated!

=CONCATENATE(LEFT(C2,1)&IF(ISNUMBER(FIND(" ",C2)),MID(C2,FIND(" ",C2)+1,1),"")&IF(ISNUMBER(FIND(" ",C2,FIND(" ",C2)+1)),MID(C2,FIND(" ",C2,FIND(" ",C2)+1)+1,1),""),"-","00")

 

[jasonb75]

The whole formula is much more complex than it needs to be, it you remove the excess it is often easier to keep track of things.

Book1
	A	B	C	D	E	F
1		well #	Patient Name	Accession	Sample ID Code	accession
2	1	A-1	ZZTEST, COV FIVE	ZCF-0051	ZCF-	09-22-271-0051
3	2	A-2	ZZTEST, COV SIX	ZCS-0053	ZCS-	09-22-271-0053
Sheet7

Cell Formulas
Range	Formula
D2:D3	D2	=LEFT(E2,4)&RIGHT(F2,4)
E2:E3	E2	=LEFT(C2)&IFERROR(MID(C2,FIND(" ",C2)+1,1),"")&IFERROR(MID(C2,FIND(" ",C2,FIND(" ",C2)+1)+1,1),"")&"-"