Split single cell value in 2 columns.

[motilulla]

Using Excel 2010

Hello, I have data in column C, column (Col "C" Cell Custom Format 00) I tried formula in column I =LEFT(C6) and in column J =RIGHT(C6) but do not getting correct split as it should be. Apart of that if I sum few numerical values result is 0.

Please help May it require VBA solution if it can be solved by formula it is perfect for me also.

Here is a dropbox link where I have uploaded file with instruction in it.
Split Cell Content.xlsm

Split Cell Content.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3			Col "C" Cell Custom Format 00
4					I want this results				Formula split does not giving correct result
5	Nº		Data		Split Col C				Formula split does not giving correct result
6	1		2M		2	M			2	do not getting
7	2		00		0	0			0	0
8	3		M1		M	1			M	1
9	4		20		2	0			2	0
10	5		M0		M	0	Correct Sum E11:E13		M	0	Sum Not Correct I11:I13
11	6		2M		2	M	5		2	M	0
12	7		10		1	0			1	0
13	8		21		2	1			2	1
14	9		M1		M	1			M	1
15	10		01		0	1	<---Correct		1	1	<--Formula Result Not Correct
16	11		12		1	2			1	2
17	12		MM		M	M			M	M
18	13		10		1	0			1	0
19	14		M0		M	0			M	0
20	15		11		1	1			1	1
21	16		11		1	1			1	1
22	17		22		2	2			2	2
23	18		M1		M	1			M	1
24	19		21		2	1			2	1
25	20		01		0	1	<---Correct		1	1	<--Formula Result Not Correct
26	21		02		0	2	<---Correct		2	2	<--Formula Result Not Correct
27	22		0M		0	M			0	M
28	23		21		2	1			2	1
29	24		M0		M	0			M	0
30	25		11		1	1			1	1
31	26		10		1	0			1	0
32	27		02		0	2	<---Correct		2	2	<--Formula Result Not Correct
33	28		00		0	0			0	0
34	29		20		2	0			2	0
35	30		02		0	2	<---Correct		2	2	<--Formula Result Not Correct
36	31		11		1	1			1	1
37	32		1M		1	M			1	M
38	33		00		0	0			0	0
39	34		10		1	0			1	0
40	35		20		2	0			2	0
41	36		21		2	1			2	1
42	37		22		2	2			2	2
43	38		MM
44	39		10
45	40		10
46	41		11
47	42		21
48	43		00
49	44		11
50	45		11
51	46		10
52	47		MM
53	48		11
54	49		M0
55	50		M0
56	51		M0
57	52		11
58	53		02
59	54		00
60	55		0M
61	56		10
62	57		M0
63	58		20
64	59		00
65	60		02
66	61		M2
67	62		M0
68	63		M2
Sheet1

Cell Formulas
Range	Formula
I6:I42	I6	=LEFT(C6)
J7:J42	J7	=RIGHT(C7)
G11,K11	G11	=SUM(E11:E13)

Regards,
Moti

 

[Artik]

Try the formulas in cells I6 and J6:

=IFERROR(VALUE(LEFT(C6)),(LEFT(C6)))
=IFERROR(VALUE(RIGHT(C6)),(RIGHT(C6)))

Artik

 

[eiloken]

TRY:

=IFERROR(VALUE(LEFT(TEXT(C6,"00"),1)),LEFT(TEXT(C6,"00"),1))

=IFERROR(VALUE(RIGHT(TEXT(C6,"00"),1)),RIGHT(TEXT(C6,"00"),1))

 

[Scott Huish]

Try the formulas in cells I6 and J6:

=IFERROR(VALUE(LEFT(C6)),(LEFT(C6)))
=IFERROR(VALUE(RIGHT(C6)),(RIGHT(C6)))

Artik

That won't work for values like 01 because 01 is not actually in the cell, perhaps:

=IFERROR(--LEFT(TEXT(C6,"00")),LEFT(TEXT(C6,"00")))
=IFERROR(--RIGHT(TEXT(C6,"00")),RIGHT(TEXT(C6,"00")))

 

[Artik]

Oh, I didn't read that the column is formatted as "00".
The direction was good, just poor execution.

Artik

 

[motilulla]

Hello, @eiloken and @Scott Huish both the option worked as request. Special thanks to both of you for solving my request. Have a nice day. Good luck.

@Artik, also thanks to you for looking my request. Have a nice day. Good luck.

Kind Regards,
Moti

 

[Tom.Jones]

maybe

=SUM(--I11:I13)

 

[Fluff]

How does that have anything to do with what the OP asked for?