Help with SUM formula

[Sean15]

Hi:
Could you help with formulas in column G? I suspect #VALUE is related to unwanted spaces. I added TRIM to formula but it not correct #VALUE error.
Thank you.

Blood work results.xlsx
	A	B	C	D	E	F	G
1		12/29/2021		3/2/2023
2	Honwell	180 mg/dL		177 mg/dL			-3
3	Trimus	45 mg/dL		47 mg/dL			#VALUE!
4	Slim	57 mg/dl		71 mg/dL			#VALUE!
5	Design	123.6 mg/dL		117 mg/dL			-6
6	Safety	2.7					#VALUE!
7	Matters	4					#VALUE!
8	Swing	11 mg/dL		13 mg/dL			#VALUE!
sheet2

Cell Formulas
Range	Formula
G2:G8	G2	=(LEFT(D2,3)-LEFT(B2,3))

 

[Skyybot]

I think the user's Excel version doesn't support TEXTBEFORE.

Oh. My bad. I am unfamiliar with checking to see what options are available with which versions. Any help on that subject would be appreciated so this doesn't happen again.

 

[Sean15]

What do these two formula return

=CODE(D3)

and

=CODE(MID(D3,3,1))

Blood work results.xlsx
	A	B	C	D	E	F	G	H
1		12/29/2021		3/2/2023
3	Trimus	45 mg/dL		47 mg/dL			52	160
sheet2

Cell Formulas
Range	Formula
G3	G3	=CODE(D3)
H3	H3	=CODE(MID(D3,3,1))

 

[Fluff]

Thanks for that, try

=(LEFT(SUBSTITUTE(D2,CHAR(160)," "),3)-LEFT(SUBSTITUTE(B2,CHAR(160)," "),3))

 

[Sean15]

Then try search:

=(LEFT(D2,SEARCH(" ",D2)-1)-LEFT(B2,SEARCH(" ",B2)-1))

Formula return #VALUE

 

[Sean15]

Thanks for that, try

=(LEFT(SUBSTITUTE(D2,CHAR(160)," "),3)-LEFT(SUBSTITUTE(B2,CHAR(160)," "),3))

Excellent, thank you. Formula in G6 and G7 returned #VALUE so I added IFERROR to take care of error.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Tom.Jones]

Try this:

=LEFT(IF(LEN(D2)>0,D2,0),3)-LEFT(B2,3)