Split the Number

[Vishaal]

Hi All,

Thanks in advance

I have the following sheet

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

04​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

45​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

65​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

02​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

90​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

06​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Want the following result

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

04​

[/td][td]

0​

[/td][td]

4​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

45​

[/td][td]

4​

[/td][td]

5​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

65​

[/td][td]

6​

[/td][td]

5​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

02​

[/td][td]

0​

[/td][td]

2​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

90​

[/td][td]

9​

[/td][td]

0​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

06​

[/td][td]

0​

[/td][td]

6​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

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

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: center"]04[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]04[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]45[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]

</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.