Formula to separate a number into individual including double 0.

[serge]

Hi everyone,
I'm looking for a formula that will return the digits from a number in separate cell even if it is 00 or 01, Thank you.

 

[alansidman]

If you are using XL2010 or a later version then with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Position" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByRepeatedLengths(1), {"Column1.1", "Column1.2"})
in
    #"Split Column by Position"

 

[serge]

Thank you very much alansidman, but I'm looking for a formula, I'm still using 2007

 

[Cubist]

How about

=LEFT(A1,1)

and

=RIGHT(A1,1)

 

[Asbestos_Jen]

If you only have two-digit numbers this will work:

Book1
	LB	LC	LD	LE	LF
5		as text		as value
6	00	0	0	0	0
7	01	0	1	0	1
8	10	1	0	1	0
9	11	1	1	1	1
Sheet1

Cell Formulas
Range	Formula
LC6:LC9	LC6	=LEFT(LB6,1)
LD6:LD9	LD6	=RIGHT(LB6,1)
LE6:LE9	LE6	=--(LEFT(LB6,1))
LF6:LF9	LF6	=--RIGHT(LB6,1)

 

[JoeMo]

Columns A:C are formatted as text. If you need the output to be numbers add a zero (e.g. =MID($A1,COLUMNS($A:B)-1,1)+0).

SpeedTest.xlsm
	A	B	C
1	00	0	0
2	01	0	1
3	10	1	0
Sheet2

Cell Formulas
Range	Formula
B1:C3	B1	=MID($A1,COLUMNS($A:B)-1,1)

 

[serge]

Thank you guys for your answers but I can't make it work !! The cell were are the 00,01,10,11 are fill with this formula :
=LOOKUP(KZ6,'New Code1'!$LZ$4:$MA$7) which is Formatted as Custom because it let me keep the double 0 and 01 which I need for my system.

My reference table is :
1 = 00
2 = 01
3 = 10
4 = 11
So for me to keep my reference table working I had to formatted the cells as Custom and when I use your formulas it's not working, maybe you could show me a different way ?

So when I use this formula for exaple :
=--(LEFT(LA6,1)) and =--RIGHT(LA6,1)

The left one don't return the right digit see in green !

What am I doing wrong ???
Thank you.

 

[Cubist]

Try replace your current lookup formula with this.

=TEXT(LOOKUP(KZ6,'New Code1'!$LZ$4:$MA$7),"00")

 

[serge]

Wow, Thank you so much Cubist it works perfect now, I really appreciate your help and everyone else with all your formulas you guys are the Greatest.
Thank you.

 

[Cubist]

You're welcome.