Convert formula to its value for use in another formula

est249

New Member
Joined
Sep 30, 2022
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGH
1well #Patient NameAccessionSample ID CodeaccessionResultedResult-Date
21A-1ZZTEST, COV FIVEZCF-0051ZCS-0009-22-271-0051
32A-2ZZTEST, COV SIXZCS-0009-22-271-0053
Sheet1
Cell Formulas
RangeFormula
D2D2=CONCATENATE(LEFT(E2,4),RIGHT(F2,4))
E2:E3E2=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))
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
You're missing a length parameter after C2.

Excel Formula:
=CONCATENATE(LEFT(C2)
 
Upvote 0
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.
 
Upvote 0
Solution
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")
 
Upvote 0
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
ABCDEF
1well #Patient NameAccessionSample ID Codeaccession
21A-1ZZTEST, COV FIVEZCF-0051ZCF-09-22-271-0051
32A-2ZZTEST, COV SIXZCS-0053ZCS-09-22-271-0053
Sheet7
Cell Formulas
RangeFormula
D2:D3D2=LEFT(E2,4)&RIGHT(F2,4)
E2:E3E2=LEFT(C2)&IFERROR(MID(C2,FIND(" ",C2)+1,1),"")&IFERROR(MID(C2,FIND(" ",C2,FIND(" ",C2)+1)+1,1),"")&"-"
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top