# Split the Number

#### Vishaal

##### Active Member
Hi All,

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

Last edited:

#### sandy666

##### Well-known Member
maybe Text to columns?

#### Peter_SSs

##### MrExcel MVP, Moderator
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
ABCDEFG
104040404
245454545
Split

#### Rick Rothstein

##### MrExcel MVP
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>

 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.

Last edited:

#### Vishaal

##### Active Member
Thanks Peter_SSs Ji

Its working for me
 F1 =LEFT(TEXT(E1,"00"))

<tbody>
</tbody>

#### Vishaal

##### Active Member
Thanks Rick Rothstein JI

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

#### Peter_SSs

##### MrExcel MVP, Moderator
Sounds like they are numbers then & Rick has the simplest solution.