Split the Number

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
269
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
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,734
Office Version
365
Platform
Windows
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
Joined
Apr 18, 2011
Messages
35,573
Office Version
2010
Platform
Windows
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

ABCDEFG
10404 0404
24545 4545

<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
CellFormula
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
Joined
Mar 16, 2019
Messages
269
Thanks Peter_SSs Ji


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

<tbody>
</tbody>

 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,734
Office Version
365
Platform
Windows
Sounds like they are numbers then & Rick has the simplest solution. :)
 

Forum statistics

Threads
1,085,932
Messages
5,386,829
Members
402,020
Latest member
tsfan74

Some videos you may like

This Week's Hot Topics

Top