Add zero to beginning of number extraction

[SaraWitch]

Hello peeps,

I have a formula to extract the first number from the previous cell and would like to add a "0" in formula if one hasn't been entered previously.

¦ MrExcel Queries.xlsm
	A	B	C
1	Contact Number	Number	Text
2	01111 111111	01111 11111
3	01111 111111 (sister's number)	01111 11111	(sister's number)
4	1111111111	1111111111
Sheet3

Cell Formulas
Range	Formula
B2:B4	B2	=LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))

Any help would be appreciated

 

[PeteWright]

try this formula:

=IFERROR(IF(NUMBERVALUE(LEFT(A2))<>0;"0"&LEFT(A2;SUM(LEN(A2)-LEN(SUBSTITUTE(A2;{"0","1","2","3","4","5","6","7","8","9"};""))));LEFT(A2;SUM(LEN(A2)-LEN(SUBSTITUTE(A2;{"0","1","2","3","4","5","6","7","8","9"};"")))));A2)

 

[SaraWitch]

Lovely! Thank you, @PeteWright! I just replaced the semicolons with a comma and it works perfectly - just wondering if there is a way for the custom format (0#### ######) to apply?

¦ MrExcel Queries.xlsm
	A	B	C	D
1	Contact Number	Number	Would like!	Text
2	01111 111111	01111 11111	01111 111111
3	01111 111111 (sister's number)	01111 11111	01111 111111	(sister's number)
4	1111111111	01111111111	01111 111111
Sheet3

Cell Formulas
Range	Formula
B2:B4	B2	=IFERROR(IF(NUMBERVALUE(LEFT(A2))<>0,"0"&LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},"")))),LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))),A2)

 

[SaraWitch]

I've got a workaround of pasting values in another column and then using Text to Columns, which does work. Would be nice to know if there is a way to do this in the formula column though...

 

[PeteWright]

@SaraWitch
I'll take a look at the number formatting later today, I think it's possible.

 

[SaraWitch]

Thank you

 

[MARK858]

@SaraWitch just wrap it in a text function

Book1
	A	B	C
1	Contact Number	Number	Would like!
2	01111 111111	01111 11111	01111 11111
3	01111 111111 (sister's number)	01111 11111	01111 11111
4	1111111111	01111111111	01111 111111
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=IFERROR(IF(NUMBERVALUE(LEFT(A2))<>0,"0"&LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},"")))),LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))),A2)
C2:C4	C2	=TEXT(IFERROR(IF(NUMBERVALUE(LEFT(A2))<>0,"0"&LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},"")))),LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))),A2),"0#### ######")

Although it appears that in your last post you aren't removing a character in column C , are you sure that is what you wanted (as the formula above is working with the same result as in column B that you stated is perfect)?

 

[Scott Huish]

I kind of think there is supposed to be 1 less 1 in that last example. If so, I have a much simpler formula that looks like it does what you want it to do.

=TEXT(MAX(--MID(SUBSTITUTE(A2," ",""),1,SEQUENCE(10))),"00000 00000")

Book1
	A	B
1	Contact Number
2	01111 111111	01111 11111
3	01111 111111 (sister's number)	01111 11111
4	111111111	01111 11111
5	567	00000 00567
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=TEXT(MAX(--MID(SUBSTITUTE(A2," ",""),1,SEQUENCE(10))),"00000 00000")

 

[SaraWitch]

Thank you, all. After changing the numbers slightly to make it easier to view, I notice that @Scott Huish solution works best (there should be 11 numbers, so I just updated accordingly):

¦ MrExcel Queries.xlsm
	A	B	C	D	E	F	G
1	Contact Number	Number	Would like!	Text	@PeteWright	@MARK858	@Scott Huish
2	01111 222333	01111 22233	01111 222333		01111 22233	01111 22233	01111 222333
3	01111 222333 (sister's number)	01111 22233	01111 222333	(sister's number)	01111 22233	01111 22233	01111 222333
4	1111222333	1111222333	01111 222333		01111222333	01111 222333	01111 222333
Sheet3

Cell Formulas
Range	Formula
E2:E4	E2	=IFERROR(IF(NUMBERVALUE(LEFT(A2))<>0,"0"&LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},"")))),LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))),A2)
F2:F4	F2	=TEXT(IFERROR(IF(NUMBERVALUE(LEFT(A2))<>0,"0"&LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},"")))),LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))),A2),"0#### ######")
G2:G4	G2	=TEXT(MAX(--MID(SUBSTITUTE(A2," ",""),1,SEQUENCE(11))),"00000 000000")
B2:B4	B2	=LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))

Thank you all so much for your help and have a lovely weekend!

 

[SaraWitch]

Just been having a play with the other formulas, and they all work (except for one CF)! So an extra big thanks to you all!

¦ MrExcel Queries.xlsm
	A	B	C	D	E	F	G
1	Contact Number	Number	Would like!	Text	@PeteWright	@MARK858	@Scott Huish
2	01111 222333	01111 222333			01111 222333	01111 222333	01111 222333
3	01111 222333 (sister's number)	01111 222333		(sister's number)	01111 222333	01111 222333	01111 222333
4	1111222333	1111222333	01111 222333		01111222333	01111 222333	01111 222333
Extract number

Cell Formulas
Range	Formula
E2:E4	E2	=IFERROR(IF(NUMBERVALUE(LEFT(A2))<>0,"0"&LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9","0"},"")))),LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9","0"},""))))),A2)
F2:F4	F2	=TEXT(IFERROR(IF(NUMBERVALUE(LEFT(A2))<>0,"0"&LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9","0"},"")))),LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9","0"},""))))),A2),"0#### ######")
G2:G4	G2	=IFERROR(TEXT(MAX(--MID(SUBSTITUTE(A2," ",""),1,SEQUENCE(11))),"00000 000000"),"")
B2:B4	B2	=LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9","0"},""))))

Wish I could mark you all as the solution, but will do @MARK858 because he got there first (even though I'm going to use @Scott Huish! )...