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]

Try this. Keep in mind that there will always have to be a space delimitator. So in your example rows 6 and 7 will still throw #VALUE errors.

=TEXTBEFORE(D2," ")-TEXTBEFORE(B2," ")

 

[Legacy 143009]

Alternatively:

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

 

[Sean15]

Try this.

=NUMBERVALUE(LEFT(D2,3))-NUMBERVALUE(LEFT(B2,3))

#VALUE error returned in G3, G4 and G8

 

[Skyybot]

I am #VALUE error in G3, G4 and G8

Sorry, I was editing when you replied. Check Post#2 again. I changed the formula.

 

[Sean15]

Alternatively:

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

Thanks, but formula returned #VALUE error.

 

[Legacy 143009]

Try this. Keep in mind that there will always have to be a space delimitator. So in your example rows 6 and 7 will still throw #VALUE errors.

=TEXTBEFORE(D2," ")-TEXTBEFORE(B2," ")

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

 

[Fluff]

@Skyybot as the OP is using xl2019 he doesn't have those functions.

 

[Fluff]

What do these two formula return

=CODE(D3)

and

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

 

[Legacy 143009]

Then try search:

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