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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
188
VikingLink
V
D
  • Question Question
How to keep a tally of matching letters in separate cells
Replies
4
Views
666
Davenil
D
A
  • Question Question
Split sheets into multiple sheets based on how many rows in cell value
Replies
3
Views
162
abdo meghari
A
A
  • Solved
How to Increment Excel cell reference by any number
2
Replies
13
Views
906
Fluff
Fluff
earthworm
  • Question Question
Split Data into separate worksheet based on Cell values
Replies
8
Views
595
earthworm
earthworm

Forum statistics

Threads
1,224,828
Messages
6,181,204
Members
453,022
Latest member
RobertV1609

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