Separate product number into individual cells.

[KyleJackMorrison]

Hello,

I have a product codes that is laid out like this:
1234-56-789-1234

Our product demand sheet requires the product numbers to be in it own cell and also without the "-".

Is there a formula or VBA code where i can convert the product number to:
A B C D E F G H I J K L M
1 2 3 4 5 6 7 8 9 1 2 3 4

Thanks in advance.
Kyle

 

[jimrward]

With your original product code in A1 try in B1

=mid(substitute($A1,”-“,”’),column()-1,1)

if this gives desired result drag across to C1, D1 etc

i am not on a pc at the moment so cannot test

 

[jmacleary]

Hi there. This does it if the structure is always the same:

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1234-56-789-1234
2	1	2	3	4	5	6	7	8	9	1	2	3	4
Sheet4

Cell Formulas
Range	Formula
A2	A2	=MID($A1,1,1)
B2	B2	=MID($A1,2,1)
C2	C2	=MID($A1,3,1)
D2	D2	=MID($A1,4,1)
E2	E2	=MID($A1,6,1)
F2	F2	=MID($A1,7,1)
G2	G2	=MID($A1,9,1)
H2	H2	=MID($A1,10,1)
I2	I2	=MID($A1,11,1)
J2	J2	=MID($A1,13,1)
K2	K2	=MID($A1,14,1)
L2	L2	=MID($A1,15,1)
M2	M2	=MID($A1,16,1)

 

[tyija1995]

Another solution if you have access to O365 dynamic array formulas:

Mapping_Doc.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1234-56-789-1234	1	2	3	4	5	6	7	8	9	1	2	3	4
Sheet14

Cell Formulas
Range	Formula
B1:N1	B1	=TRANSPOSE(MID(SUBSTITUTE($A1,"-",""),ROW(INDIRECT("$1:$"&LEN(SUBSTITUTE($A1,"-","")))),1))
Dynamic array formulas.

Note if A1 is extended, then B1 will extend too as it is dynamic.

 

[jmacleary]

I love jimrwards answer - better than mine - but it needs a tweak (a straight copy brings the wrong sort of double quote in, and the -1 isn't needed): try this in B1 and drag across:
=MID(SUBSTITUTE($A1,"-",""),COLUMN(),1)

 

[jimrward]

@jmacleary the -1 is required As column() in B will yield 2 so you need to offset by 1 to pick up first substring

i am working from an iPad hence the naff double quotes

 

[tyija1995]

I love jimrwards answer - better than mine - but it needs a tweak (a straight copy brings the wrong sort of double quote in, and the -1 isn't needed): try this in B1 and drag across:
=MID(SUBSTITUTE($A1,"-",""),COLUMN(),1)

As @jimrward said he is starting in the 2nd column so needs to OFFSET by -1, otherwise use COLUMNS($B1:B1) and then you can drag across as an expanding reference.

 

[jimrward]

If you want your numbers to appear in say D1 onwards then -3 would be the offset basically you need to offset to the first character of your substituted string

 

[Fluff]

Alternatively, you could use
=MID(SUBSTITUTE($A1,"-",""),COLUMN(A1),1)
So that you don't have to worry about offsets

 

[jmacleary]

Not quoting all - I was wrong in not reading Jim's solution as itscorrect if it starts in B1, however the OP wanted the result to start in A column, thats why I removed the -1. And of course fluff has the best answer.