How to split a range of numbers from one cell into a column of multiple cells all with individual numbers

Z

zhang66777

New Member
Joined
Jan 6, 2015
Messages
7
Hi, all

I have a huge sheet as Table 1, the data is indicating the location of 10x10 box, I would like to change it to table 2 and on, would you please let me know how to do it? many many thanks in advance

Julie
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1-5[/TD]
[/TR]
[TR]
[TD]B6-9[/TD]
[/TR]
[TR]
[TD]F6-9[/TD]
[/TR]
[TR]
[TD]G8-H2[/TD]
[/TR]
[TR]
[TD]I2-6[/TD]
[/TR]
[TR]
[TD]H7,8[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
zhang66777 said:
Hi, all

I have a huge sheet as Table 1, the data is indicating the location of 10x10 box, I would like to change it to table 2 and on, would you please let me know how to do it? many many thanks in advance

Julie
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1-5[/TD]
[/TR]
[TR]
[TD]B6-9[/TD]
[/TR]
[TR]
[TD]F6-9[/TD]
[/TR]
[TR]
[TD]G8-H2[/TD]
[/TR]
[TR]
[TD]I2-6[/TD]
[/TR]
[TR]
[TD]H7,8[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


Are these the desired outcomes (only in a table to validate output) - presumably you want a single long, continual column of results...

15z1qc5.jpg


Could you explain how the code G8-H2 should be coded


Is that just

G8
H2

or a range, somehow, of G8-H2?

G2
G3
G4
G5
G6
G7
G8
H2
H3
H4
H5
H6
H7
H8

(like an Excel range, for example)


or even

G2
G3
G4
G5
G6
G7
G8
H2

(like a partial range??)
 
Last edited:
Upvote 0
many thanks for your reply,

1. I will need a single long, continual column
A1
A2
A3
A4
A5
B6
B7
B8
B9
F6
...
2. the box has 10 column and 10 row, so G8-H2 will be
G8
G9
G10
H1
H2

3. Actually I have 3 columns, the first is Sample ID, 2nd is box number, 3rd is the one I have listed earlier, like this:
[TABLE="width: 316"]
<tbody>[TR]
[TD]CHWTB-107335
[/TD]
[TD="align: right"]1
[/TD]
[TD] A1-6
[/TD]
[/TR]
</tbody>[/TABLE]

I would like it become: Many many thanks

[TABLE="width: 316"]
<tbody>[TR]
[TD]CHWTB-107335-S-1[/TD]
[TD="align: right"]1 [/TD]
[TD] A1[/TD]
[/TR]
[TR]
[TD]CHWTB-107335-S-2[/TD]
[TD="align: right"]1[/TD]
[TD] A2[/TD]
[/TR]
[TR]
[TD]CHWTB-107335-S-3[/TD]
[TD="align: right"]1[/TD]
[TD] A3[/TD]
[/TR]
[TR]
[TD]CHWTB-107335-S-4[/TD]
[TD="align: right"]1[/TD]
[TD] A4[/TD]
[/TR]
[TR]
[TD]CHWTB-107335-S-5[/TD]
[TD="align: right"]1[/TD]
[TD] A5[/TD]
[/TR]
[TR]
[TD]CHWTB-107335-S-6[/TD]
[TD="align: right"]1[/TD]
[TD] A6[/TD]
[/TR]
</tbody><colgroup><col><col span="2"></colgroup>[/TABLE]
 
Last edited:
Upvote 0
zhang66777 said:
many thanks for your reply,

1. I will need a single long, continual column
A1
A2
A3
A4
A5
B6
B7
B8
B9
F6
...
2. the box has 10 column and 10 row, so G8-H2 will be
G8
G9
G10
H1
H2

3. Actually I have 3 columns, the first is Sample ID, 2nd is box number, 3rd is the one I have listed earlier, like this:
[TABLE="width: 316"]
<tbody>[TR]
[TD]CHWTB-107335[/TD]
[TD="align: right"]1[/TD]
[TD] A1-6[/TD]
[/TR]
</tbody>[/TABLE]

I would like it become: Many many thanks

[TABLE="width: 316"]
<tbody>[TR]
[TD]CHWTB-107335-S-1[/TD]
[TD="align: right"]1[/TD]
[TD] A1[/TD]
[/TR]
[TR]
[TD]CHWTB-107335-S-2[/TD]
[TD="align: right"]1[/TD]
[TD] A2[/TD]
[/TR]
[TR]
[TD]CHWTB-107335-S-3[/TD]
[TD="align: right"]1[/TD]
[TD] A3[/TD]
[/TR]
[TR]
[TD]CHWTB-107335-S-4[/TD]
[TD="align: right"]1[/TD]
[TD] A4[/TD]
[/TR]
[TR]
[TD]CHWTB-107335-S-5[/TD]
[TD="align: right"]1[/TD]
[TD] A5[/TD]
[/TR]
[TR]
[TD]CHWTB-107335-S-6[/TD]
[TD="align: right"]1[/TD]
[TD] A6[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


Is the end of the FIRST code always suffixed with "-S-n" (where n is the number of instances)??
 
Upvote 0
Is this correct?




Excel 2010
ABC
Code
CHWTB-107335
CHWTB-107336
CHWTB-107337
CHWTB-107338
CHWTB-107339
CHWTB-107340
CHWTB-107341

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Qty[/TD]
[TD="align: center"]BoxCode[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]A1-6[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]A1-5[/TD]

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

[TD="align: center"]3[/TD]
[TD="align: center"]B6-9[/TD]

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

[TD="align: center"]4[/TD]
[TD="align: center"]F6-9[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]G8-H2[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]I2-6[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]H7,8[/TD]

</tbody>
Sheet1​




Excel 2010
ABC

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Code[/TD]
[TD="align: center"]Qty[/TD]
[TD="align: center"]BoxCode[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]CHWTB-107335-S-1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A1[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]CHWTB-107335-S-2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A2[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]CHWTB-107335-S-3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A3[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]CHWTB-107335-S-4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A4[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]CHWTB-107335-S-5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A5[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]CHWTB-107335-S-6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A6[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]CHWTB-107336-S-1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]A1[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]CHWTB-107336-S-2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]A2[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]CHWTB-107336-S-3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]A3[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]CHWTB-107336-S-4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]A4[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]CHWTB-107336-S-5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]A5[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]CHWTB-107337-S-1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]B6[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]CHWTB-107337-S-2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]B7[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]CHWTB-107337-S-3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]B8[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]CHWTB-107337-S-4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]B9[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]CHWTB-107338-S-1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]F6[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]CHWTB-107338-S-2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]F7[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]CHWTB-107338-S-3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]F8[/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]CHWTB-107338-S-4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]F9[/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]CHWTB-107339-S-1[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]G8[/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]CHWTB-107339-S-2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]G9[/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]CHWTB-107339-S-3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]G10[/TD]

[TD="align: center"]24[/TD]
[TD="align: center"]CHWTB-107339-S-4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]H1[/TD]

[TD="align: center"]25[/TD]
[TD="align: center"]CHWTB-107339-S-5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]H2[/TD]

[TD="align: center"]26[/TD]
[TD="align: center"]CHWTB-107340-S-1[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]I2[/TD]

[TD="align: center"]27[/TD]
[TD="align: center"]CHWTB-107340-S-2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]I3[/TD]

[TD="align: center"]28[/TD]
[TD="align: center"]CHWTB-107340-S-3[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]I4[/TD]

[TD="align: center"]29[/TD]
[TD="align: center"]CHWTB-107340-S-4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]I5[/TD]

[TD="align: center"]30[/TD]
[TD="align: center"]CHWTB-107340-S-5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]I6[/TD]

[TD="align: center"]31[/TD]
[TD="align: center"]CHWTB-107341-S-1[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]H7[/TD]

[TD="align: center"]32[/TD]
[TD="align: center"]CHWTB-107341-S-2[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]H8[/TD]

</tbody>
Sheet2​
 
Upvote 0
Sorry that I was away, yes, this is correct, but I didn't see the trick, how can I do it? By the way, Column B is Box rather than quantity.

many many thanks

Julie
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Show duplicates from across multiple ranges in one cell with a single nested formula?
Replies
9
Views
175
VikingLink
V
D
  • Question Question
How to keep a tally of matching letters in separate cells
Replies
4
Views
570
Davenil
D
A
  • Question Question
Split sheets into multiple sheets based on how many rows in cell value
Replies
3
Views
154
abdo meghari
A
A
  • Solved
How to Increment Excel cell reference by any number
2
Replies
13
Views
813
Fluff
Fluff
earthworm
  • Question Question
Split Data into separate worksheet based on Cell values
Replies
8
Views
593
earthworm
earthworm

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top