Split ranges(001-005) but keep the associated column

[norriz]

I have table with a range of numbers with the associated name(below), need to find a way to split ex:001-005 to 001 and table1, 002 and table1, 003 and table1. Can this be done in access or excel. Help needed urgently. Thank you

Number	Name
001-005	table1
006-010	table2

I'd like my final data to look like this-
Number Name
001 Table1
002 Table1

 

[kweaver]

(a) which version of Excel do you run (please update your account; (b) are there always 5 numbers in the range; (c) do you want a formula or is VBA ok?

Here's one way making a few assumptions.

MergeCells05202021.xlsm
	A	B	C	D	E	F	G
1	Number	Name
2	001-005	table1	001 table1	002 table1	003 table1	004 table1	005 table1
3	006-010	table2	006 table2	007 table2	008 table2	009 table2	010 table2
Sheet6

Cell Formulas
Range	Formula
C2:G3	C2	=TEXT(LEFT($A2,3)+COLUMN()-3,"000")&" "&$B2

 

[norriz]

I have excel for Microsoft 365 MSO, I hope thats the right version.
I number ranges varies. You have a few variables like below:

150-154, 156, 160-165, 260, 265, 439- I will like ot break down150-154 but also keep the associated column 360-361, 363-364, 367-368 I dont know VBA if you show me I am sure I can run a query with it. Thanks Kweaver.

 

[kweaver]

Here's another approach:

MergeCells05202021.xlsm
	A	B	C	D	E	F	G
5	001-005	table1	001 table1	002 table1	003 table1	004 table1	005 table1
6	006-010	table2	006 table2	007 table2	008 table2	009 table2	010 table2
Sheet6

Cell Formulas
Range	Formula
C5:G6	C5	=TEXT(SEQUENCE(,5,LEFT(A5,3)),"000") & " " & $B5
Dynamic array formulas.

 

[kweaver]

I have excel for Microsoft 365 MSO, I hope thats the right version.
I number ranges varies. You have a few variables like below:

150-154, 156, 160-165, 260, 265, 439- I will like ot break down150-154 but also keep the associated column 360-361, 363-364, 367-368 I dont know VBA if you show me I am sure I can run a query with it. Thanks Kweaver.

MergeCells05202021.xlsm
	A	B	C	D	E	F	G
1	Number	Name
2	001-005	table1	001 table1	002 table1	003 table1	004 table1	005 table1
3	006-010	table2	006 table2	007 table2	008 table2	009 table2	010 table2
4	150-154	table3	150 table3	151 table3	152 table3	153 table3	154 table3
5	156	table4	156 table4
6	260	table5	260 table5
7	367-368	table6	367 table6	368 table6
8	439-441	table7	439 table7	440 table7	441 table7
Sheet6

Cell Formulas
Range	Formula
C8:E8,C7:D7,C5:C6,C2:G4	C2	=TEXT(SEQUENCE(,1+RIGHT($A2,3)-LEFT($A2,3),LEFT($A2,3)),"000") & " " & $B2
Dynamic array formulas.

Only need the formula in C2 and fill down.

 

[norriz]

Thanks Kweaker. I do like your approach, however I did not want to merge the columns,but the results remain the same . Let me take a stab at this and let you know. I appreciate the quick response

 

[kweaver]

Read this as merging them: "ex:001-005 to 001 and table1, 002 and table1, 003 and table1."
Maybe be more specific, then, if this isn't it.

 

[norriz]

Kweaker, how do you address the situation like this-

301-303, 311, 399 in the same cell. Thank you

 

[kweaver]

First of all, it's "kweaver" not "kweaker".
Then, what determines what goes in what cell? All the 300s, then all the 400s, or what's the logic?

 

[norriz]

Read this as merging them: "ex:001-005 to 001 and table1, 002 and table1, 003 and table1."
Maybe be more specific, then, if this isn't it.

Sorry for not being more specific .
So here is current state

Numbers Description 003, 010-043, 045, 048, 050-067, 068-079, 085-089, 100-149, 167, 200-212, 214-241, 243-253, 255-259, 261-264, 266-268, 270-297, 299, 313-317, 320-342, 344, 346-347, 349, 365-366, 369, 387, 389-397, 398, 411-412, 415-416, 700-701, 703-708, 710-714, 716-722, 725-729, 750, 754-759 Tables Future state Numbers Description 003 tables 010 tables 011 tables