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

[zhang66777]

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

A1-5

B6-9

F6-9

G8-H2

I2-6

H7,8

<tbody>
</tbody>

A1

A2

A3

A4

A5

<tbody>
</tbody>

 

[MartyS]

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

A1-5

B6-9

F6-9

G8-H2

I2-6

H7,8

<tbody>
</tbody>

A1

A2

A3

A4

A5

<tbody>
</tbody>

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

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??)

 

[zhang66777]

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:

CHWTB-107335

1

A1-6

<tbody>
</tbody>

I would like it become: Many many thanks

CHWTB-107335-S-1	1	A1
CHWTB-107335-S-2	1	A2
CHWTB-107335-S-3	1	A3
CHWTB-107335-S-4	1	A4
CHWTB-107335-S-5	1	A5
CHWTB-107335-S-6	1	A6

<tbody>
</tbody><colgroup><col><col span="2"></colgroup>

 

[MartyS]

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:

CHWTB-107335

1

A1-6

<tbody>
</tbody>

I would like it become: Many many thanks

CHWTB-107335-S-1	1	A1
CHWTB-107335-S-2	1	A2
CHWTB-107335-S-3	1	A3
CHWTB-107335-S-4	1	A4
CHWTB-107335-S-5	1	A5
CHWTB-107335-S-6	1	A6

<tbody>
</tbody>

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

 

[MartyS]

Is this correct?




Excel 2010

	A	B	C
1	Code	Qty	BoxCode
2	CHWTB-107335	1	A1-6
3	CHWTB-107336	2	A1-5
4	CHWTB-107337	3	B6-9
5	CHWTB-107338	4	F6-9
6	CHWTB-107339	5	G8-H2
7	CHWTB-107340	6	I2-6
8	CHWTB-107341	7	H7,8

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Excel 2010

	A	B	C
1	Code	Qty	BoxCode
2	CHWTB-107335-S-1	1	A1
3	CHWTB-107335-S-2	1	A2
4	CHWTB-107335-S-3	1	A3
5	CHWTB-107335-S-4	1	A4
6	CHWTB-107335-S-5	1	A5
7	CHWTB-107335-S-6	1	A6
8	CHWTB-107336-S-1	2	A1
9	CHWTB-107336-S-2	2	A2
10	CHWTB-107336-S-3	2	A3
11	CHWTB-107336-S-4	2	A4
12	CHWTB-107336-S-5	2	A5
13	CHWTB-107337-S-1	3	B6
14	CHWTB-107337-S-2	3	B7
15	CHWTB-107337-S-3	3	B8
16	CHWTB-107337-S-4	3	B9
17	CHWTB-107338-S-1	4	F6
18	CHWTB-107338-S-2	4	F7
19	CHWTB-107338-S-3	4	F8
20	CHWTB-107338-S-4	4	F9
21	CHWTB-107339-S-1	5	G8
22	CHWTB-107339-S-2	5	G9
23	CHWTB-107339-S-3	5	G10
24	CHWTB-107339-S-4	5	H1
25	CHWTB-107339-S-5	5	H2
26	CHWTB-107340-S-1	6	I2
27	CHWTB-107340-S-2	6	I3
28	CHWTB-107340-S-3	6	I4
29	CHWTB-107340-S-4	6	I5
30	CHWTB-107340-S-5	6	I6
31	CHWTB-107341-S-1	7	H7
32	CHWTB-107341-S-2	7	H8

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

 

[MartyS]

If so... this should do the trick: https://www.dropbox.com/s/qxs6xch5a...ell into a column of multiple cells.xlsm?dl=0


Note: not all error checking is in place. If your data doesn't follow the expected format.. the program may result in an exception/error... but I can add the error-handling in (the code is in place, I just didn't finish it quite yet).

 

[zhang66777]

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

 

[zhang66777]

I only saw the sheet 1 and sheet 2 data, but not any macro or any formula...

 

[Peter_SSs]

If so... this should do the trick: https://www.dropbox.com/s/qxs6xch5a...ell into a column of multiple cells.xlsm?dl=0

Please post any suggested macros directly in the thread. Refer to #4 of the Forum Rules (paragraphs 3 & 4)