Separating numbers into 10s, 100s and 1000s in different Columns.

CT Chua · Mar 12, 2018

Would appreciate if anyone could help me out with this problem.

[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD="width: 127"] A1
[/TD]
[TD="width: 106"] B1
[/TD]
[TD="width: 95"] C1
[/TD]
[TD="width: 95"] D1
[/TD]
[/TR]
[TR]
[TD="width: 127"]

7841123

[/TD]
[TD="width: 106"]

784

[/TD]
[TD="width: 95"]

11

[/TD]
[TD="width: 95"]

23

[/TD]
[/TR]
[TR]
[TD="width: 127"]

8

[/TD]
[TD="width: 106"]
[/TD]
[TD="width: 95"]
[/TD]
[TD="width: 95"]
[/TD]
[/TR]
[TR]
[TD="width: 127"]

37990865

[/TD]
[TD="width: 106"]
[/TD]
[TD="width: 95"]
[/TD]
[TD="width: 95"]
[/TD]
[/TR]
[TR]
[TD="width: 127"]

93714

[/TD]
[TD="width: 106"]
[/TD]
[TD="width: 95"]
[/TD]
[TD="width: 95"]
[/TD]
[/TR]
[TR]
[TD="width: 127"]

136

[/TD]
[TD="width: 106"]
[/TD]
[TD="width: 95"]
[/TD]
[TD="width: 95"]
[/TD]
[/TR]
[TR]
[TD="width: 127"]

11986746

[/TD]
[TD="width: 106"]
[/TD]
[TD="width: 95"]
[/TD]
[TD="width: 95"]
[/TD]
[/TR]
</tbody>[/TABLE]

I need to separate the numbers in A1 into 10s in D1, 100s in C1 and 1000s in B1.

1. Can this be done without using VBA and what will be the formula?

2. How can it be done in VBA?

Thanks

Johnny Thunder · Mar 12, 2018

B1 = =LEFT(E4,3)
C1 = =MID(E4,4,2)
D1 = =RIGHT(E4,2)

this will just yeild the same results you had in the grid.

CT Chua said:
 Would appreciate if anyone could help me out with this problem.

[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD] A1[/TD]
[TD] B1[/TD]
[TD="width: 95"] C1[/TD]
[TD="width: 95"] D1[/TD]
[/TR]
[TR]
[TD="width: 127"]
7841123
[/TD]
[TD="width: 106"]
784
[/TD]
[TD="width: 95"]
11
[/TD]
[TD="width: 95"]
23
[/TD]
[/TR]
[TR]
[TD="width: 127"]
8
[/TD]
[TD="width: 106"][/TD]
[TD="width: 95"][/TD]
[TD="width: 95"][/TD]
[/TR]
[TR]
[TD="width: 127"]
37990865
[/TD]
[TD="width: 106"][/TD]
[TD="width: 95"][/TD]
[TD="width: 95"][/TD]
[/TR]
[TR]
[TD="width: 127"]
93714
[/TD]
[TD="width: 106"][/TD]
[TD="width: 95"][/TD]
[TD="width: 95"][/TD]
[/TR]
[TR]
[TD="width: 127"]
136
[/TD]
[TD="width: 106"][/TD]
[TD="width: 95"][/TD]
[TD="width: 95"][/TD]
[/TR]
[TR]
[TD="width: 127"]
11986746
[/TD]
[TD="width: 106"][/TD]
[TD="width: 95"][/TD]
[TD="width: 95"][/TD]
[/TR]
</tbody>[/TABLE]

I need to separate the numbers in A1 into 10s in D1, 100s in C1 and 1000s in B1.

1. Can this be done without using VBA and what will be the formula?

2. How can it be done in VBA?

Thanks

Eric W · Mar 12, 2018

Here's the formula version:

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7841123[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]784[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]23[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]37990865[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3799[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]65[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]93714[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]37[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]14[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]136[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]36[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]11986746[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1198[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]67[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]46[/TD]

</tbody>

Sheet7

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=INT(A1/10000)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=INT(MOD(A1,10000)/100)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]=MOD(A1,100)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

CT Chua · Mar 12, 2018

Eric W said:
Here's the formula version:

A B C D

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]7841123[/TD]
[TD="align: right"]784[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]37990865[/TD]
[TD="align: right"]3799[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]65[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]93714[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]136[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]36[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]11986746[/TD]
[TD="align: right"]1198[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]46[/TD]

</tbody>
Sheet7

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"]=INT(A1/10000)[/TD]
[/TR]
[TR]
[TH]C1[/TH]
[TD="align: left"]=INT(MOD(A1,10000)/100)[/TD]
[/TR]
[TR]
[TH]D1[/TH]
[TD="align: left"]=MOD(A1,100)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks a millions.....................appreciate all the help from all above.

CT Chua · Mar 13, 2018

I am new with Forum, just started using them Last month.

Question is how do i Mark my case as close and Rate it.

Thanks and appreciate all the help.

Eric W · Mar 13, 2018

As a rule, this forum does not close threads. The idea is that sometimes someone comes along with another idea later that could add to the discussion. I've seen people add messages to threads that have been idle for years.

If you get an answer you're happy with, you can post a message to that effect. That's usually nice to do, so that people won't continue to work on a problem.

If you want to rate/show appreciation for a specific message, you can click the "Thank you for posting this" and "Like this post" options on the bottom left of each message. If you want to rate the entire thread, on the top right of the web page is a menu bar with the option "Rate this thread". Click on that and choose your rating.

Glad we could help!

Separating numbers into 10s, 100s and 1000s in different Columns.

CT Chua

New Member

Excel Facts

Johnny Thunder

Well-known Member

Eric W

MrExcel MVP

CT Chua

New Member

CT Chua

New Member

Eric W

MrExcel MVP

Similar threads

Forum statistics

Share this page

Separating numbers into 10s, 100s and 1000s in different Columns.

CT Chua

New Member

Excel Facts

Johnny Thunder

Well-known Member

Eric W

MrExcel MVP

CT Chua

New Member

CT Chua

New Member

Eric W

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock