# Thread: Split the Number Thanks: 0 Likes: 0

1. ## Split the Number

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  Reply With Quote

2. ## Re: Split the Number

maybe Text to columns?  Reply With Quote

3. ## Re: Split the Number

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.

Split

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

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

Excel tables to the web >> Excel Jeanie HTML 4  Reply With Quote

4. ## Re: Split the Number Originally Posted by 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

Split

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

 Cell Formula B1 =LEFT(A1) C1 =RIGHT(A1) F1 =LEFT(TEXT(E1,"00")) G1 =RIGHT(E1)
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.  Reply With Quote

5. ## Re: Split the Number

Thanks Peter_SSs Ji

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

6. ## Re: Split the Number

Thanks Rick Rothstein JI

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

7. ## Re: Split the Number

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

## User Tag List

excel, result, sheet, sheet1, split the number 