Split the Number

[Vishaal]

Hi All,

Thanks in advance

I have the following sheet

Excel 2010 32 bit

	A​
1​	04​
2​	45​
3​	65​
4​	02​
5​	90​
6​	06​

Sheet: Sheet1

Want the following result

Excel 2010 32 bit

	A​	B​	C​
1​	04​	0​	4​
2​	45​	4​	5​
3​	65​	6​	5​
4​	02​	0​	2​
5​	90​	9​	0​
6​	06​	0​	6​

Sheet: Sheet1

tried the following but not find the result

1) IF(VALUE(MID($A2,COLUMNS($B$1:B1),1))=0,"",MID($A2,COLUMNS($B$1:B1),1))

2) =MID($A2,COLUMNS($B$1:B1),1)
​

Help pls

 

[sandy666]

maybe Text to columns?

 

[Peter_SSs]

If the values are Text then try columns B:B but if the values are Numbers formatted to show leading zeros then try columns F:G and if you haven't given us a fully representative sample, then please do so.

Excel Workbook
	A	B	C	D	E	F	G
1	04	0	4		04	0	4
2	45	4	5		45	4	5
Split

 

[Rick Rothstein]

If the values are Text then try columns B:B but if the values are Numbers formatted to show leading zeros then try columns F:G

Split

	A	B	C	D	E	F	G
1	04	0	4		04	0	4
2	45	4	5		45	4	5

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:33px;"><col style="width:26px;"><col style="width:26px;"><col style="width:26px;"><col style="width:33px;"><col style="width:26px;"><col style="width:26px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =LEFT(A1) C1 =RIGHT(A1) F1 =LEFT(TEXT(E1,"00")) G1 =RIGHT(E1) <tbody> </tbody>

<tbody>
</tbody>

Another set of formulas that will work whether the numbers are text or formatted numbers...

Left Digit: =INT(E1/10)

Right Digit: =MOD(E1,10)

Note: These formulas return numeric values, not text values.

 

[Vishaal]

Thanks Peter_SSs Ji


Its working for me

F1	=LEFT(TEXT(E1,"00"))

<tbody>
</tbody>

 

[Vishaal]

Thanks Rick Rothstein JI

its working for me Left Digit: =INT(E1/10)

 

[Peter_SSs]

Sounds like they are numbers then & Rick has the simplest solution.