Why not row is summing after the separating join text

[Kishan]

Using Excel 2000</SPAN></SPAN>

Hi,</SPAN></SPAN>

I got 2 columns B & C in these columns there is a join text I want separate them in columns E, F, G, H for that I am using the formula below the problem is that if i want to sum rows E:H why I am getting 0? Please need help</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4
5		C	D		C	C	D	D		Sum
6		1|1	2|1		1	1	2	1		0
7		1|1	2|1		1	1	2	1		0
8		1|1	2|1		1	1	2	1		0
9		1|1	X|2		1	1	X	2		0
10		1|1	1|X		1	1	1	X		0
11		1|1	X|2		1	1	X	2		0
12		1|1	1|2		1	1	1	2		0
13		2|2	X|2		2	2	X	2		0
14		2|2	X|2		2	2	X	2		0
15		1|1	X|X		1	1	X	X		0
16		1|X	2|X		1	X	2	X		0
17		X|X	2|X		X	X	2	X		0
18
19
Sheet1

Cell Formulas
Range	Formula
E6	=LEFT(B6,1)
F6	=RIGHT(B6,1)
G6	=LEFT(C6,1)
H6	=RIGHT(C6,1)
J6	=SUM(E6:H6)

Thank you in advance</SPAN></SPAN>

Regards,</SPAN>
Kishan</SPAN>

 

[Fluff]

Both LEFT & RIGHT (along with MID) will return a string value, rather than a number
Try
=IF(ISERROR(LEFT(C6,1)*1),LEFT(C6,1),LEFT(C6,1)*1)

 

[Kishan]

Both LEFT & RIGHT (along with MID) will return a string value, rather than a number
Try
=IF(ISERROR(LEFT(C6,1)*1),LEFT(C6,1),LEFT(C6,1)*1)

Thank you Fluff, yes your formula did the trick, now I am getting the sum of each row perfectly</SPAN></SPAN>

Kind Regards,</SPAN>
Kishan</SPAN></SPAN>

 

[Fluff]

You're welcome & thanks for the feedback