Extracting certain data from merged cell into unmerged cells

[bamacwby]

If I have cells A1 & B1 merged with the data 1957 in that merged cell, how can I put the 1 into cell A4, the 9 in cell B4, 5 in C4 and 7 in D4? Also if I have the same data (1957) in cell M3 how can I put a single digit in a single cell, like A9 and so on? Thanks

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Snakehips]

@bamacwby Does this help?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1957
2
3
4	1	9	5	7									1957
5
6
7
8
9	1	9	5	7
10
Sheet4

Cell Formulas
Range	Formula
A4:D4	A4	=MID($A1,COLUMN(A4:D4),1)
A9:D9	A9	=MID($M4,COLUMN(A4:D4),1)
Dynamic array formulas.

 

[bamacwby]

@bamacwby Does this help?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1957
2
3
4	1	9	5	7									1957
5
6
7
8
9	1	9	5	7
10
Sheet4

Cell Formulas
Range	Formula
A4:D4	A4	=MID($A1,COLUMN(A4:D4),1)
A9:D9	A9	=MID($M4,COLUMN(A4:D4),1)
Dynamic array formulas.

Thank You very much

 

[Cubist]

Another option.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1957
2
3													1957
4	1	9	5	7
5
6
7
8
9	1	9	5	7
10
Sheet7

Cell Formulas
Range	Formula
A4:D4	A4	=MID(A1,SEQUENCE(,LEN(A1)),1)
A9:D9	A9	=MID(M3,SEQUENCE(,LEN(M3)),1)
Dynamic array formulas.

 

[bamacwby]

Thank You very much

What if the 1957 is on another sheet? How would that formula look?

 

[Fluff]

Just put the sheet name before the cell reference like 'Sheet 1'!A1

 

[bamacwby]

@bamacwby Does this help?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1957
2
3
4	1	9	5	7									1957
5
6
7
8
9	1	9	5	7
10
Sheet4

Cell Formulas
Range	Formula
A4:D4	A4	=MID($A1,COLUMN(A4:D4),1)
A9:D9	A9	=MID($M4,COLUMN(A4:D4),1)
Dynamic array formulas.

Snakeships,
When I typed the formula +mid('engine formulas'!m3,column(engine!z5:ad5),1) I've got ## in cell z5 with the error of "we can't spill into merged cell" How do I correct this?

 

[Snakehips]

You must have a merged cell 'obstructing' the spilling/displaying of the resultant array of that formula.
Similar to having another formula or hard written value in a cell that is too close to the elected spill result.

Check that there is not an unexpected random merged cell an underage it.
If there is a merged cell that is a requisite part of your data layout then you will need to adjust to suit.

 

[bamacwby]

Another option.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1957
2
3													1957
4	1	9	5	7
5
6
7
8
9	1	9	5	7
10
Sheet7

Cell Formulas
Range	Formula
A4:D4	A4	=MID(A1,SEQUENCE(,LEN(A1)),1)
A9:D9	A9	=MID(M3,SEQUENCE(,LEN(M3)),1)
Dynamic array formulas.

now, another question, can i put the same formula in cell d10 and have it go right to left if I have a 0 in cell m4? because sometimes the data in m4 might be 4 or 5 digits