Concatenate Merge & normal cell

[jasontoh]

Hi,

Pls help me to make CONCATENATE formula

A1:D1 is merge cell with content "XX"
A2 = "11", B2 ="12", C2 ="13", D2="14"
How can I make a formula to CONCATENATE row 1 & 2 to get row 3 as:
A3 ="XX11", B3 = "XX12", C3="XX13", D3="XX14"

any help is appreciated.

Jason

 

[Robert Mika]

Hi Jason and welcome to the board
Try:
=A1&A2

 

[jasontoh]

Hi Robert,

Thanks for the quick reply
If I copy the formula A3 to B3, the result shows "12"
I would like to get "XX12"
The problem is B1 although it is merge with A1, content is blank for excel
Any way to overcome this?

Jason

 

[Robert Mika]

Is B1 till D1 populate with "XX"?
And is "XX" a real XX or any other content?

 

[jasonb75]

Try

=$A1&A2

 

[jasontoh]

Hi Robert,

I post a pic:
Row 3 is what I want to achieve by CONCATENATE formula

Excel Workbook
	A	B	C	D
1	XX
2	11	12	13	14
3	XX11	XX12	XX13	XX14
Sheet1

 

[jasontoh]

Hi JasonB75,

This works for this example
But I have a long row with different merge size
I need one general formula

Post another pic
Again, row 3 is what 1 want to achieve by formula

Excel Workbook
	A	B	C	D	E	F	G
1	XX				YY
2	11	12	13	14	21	22	23
3	XX11	XX12	XX13	XX14	YY21	YY22	YY23
Sheet1

 

[jasonb75]

In that case try

=INDEX($A1:A1,MATCH("ZZZZZ",$A1:A1))&A2

When you merge cells you don't merge the content of one into all of them although it might appear that way. In your example B1, C1 and D1 all become A1, the original cells no longer exist.

 

[jasontoh]

Hi JasonB75,

It works!
I'll have to study the formula

Thanks again
Jason

 

[jasontoh]

I'm concern about the MATCH portion of the formula, it is not foolproof.

* If A1 contains numbers it don't work
* If row 1 contain both characters & numbers wrong selection will happen

Anyway to correct this problem?

Pic:

Excel Workbook
	A	B	C	D	E	F	G
1	A1				11
2	A1	12	13	14	21	22	23
3	A1A1	A112	A113	A114	A121	A122	A123
Sheet1