Cell fusion and different result

[seanxx]

in the cells is filled
'TEST'!01 and 'TEST'!02
but when they are combined there is a result
TEST'!01_and_TEST'!02
Could you please advise me on this?

thank you

Zošit1
	I	J	K
1		TEST'!01
2	_and_	TEST'!02
3		TEST'!01_and_TEST'!02
Hárok1

Cell Formulas
Range	Formula
J3	J3	=CONCATENATE(J1,I2,J2)

 

[shift-del]

Hi

The first single quote tells Excel that everything that follows should be considered as text. Consequently the first single quote doesn't pick up in concatenation.
As a workaround I would suggest the solution in I5/I6.

Mappe11
	I	J
1		TEST'!01
2	_and_	TEST'!02
3		TEST'!01_and_TEST'!02
4
5		'TEST'!01
6	_and_	'TEST'!02
7		'TEST'!01_and_'TEST'!02
Tabelle1

Cell Formulas
Range	Formula
J3,J7	J3	=CONCATENATE(J1,I2,J2)
J5	J5	="'TEST'!01"
J6	J6	="'TEST'!02"

 

[Peter_SSs]

One example is not much to go on but if the col J values will always begin with a single quote mark, would this work for you?

24 03 23.xlsm
	I	J
1		TEST'!01
2	_and_	TEST'!02
3		'TEST'!01_and_'TEST'!02
CONCAT

Cell Formulas
Range	Formula
J3	J3	=CONCAT(I1:I2&"'"&J1:J2)

 

[seanxx]

Hi

The first single quote tells Excel that everything that follows should be considered as text. Consequently the first single quote doesn't pick up in concatenation.
As a workaround I would suggest the solution in I5/I6.

Mappe11
	I	J
1		TEST'!01
2	_and_	TEST'!02
3		TEST'!01_and_TEST'!02
4
5		'TEST'!01
6	_and_	'TEST'!02
7		'TEST'!01_and_'TEST'!02
Tabelle1

Cell Formulas
Range	Formula
J3,J7	J3	=CONCATENATE(J1,I2,J2)
J5	J5	="'TEST'!01"
J6	J6	="'TEST'!02"

Thanks for the explanation,
Now I understand.

In the original formula, which I did not post here
was the content of cell J1= 'TEST'!01
as a link from another sheet.
I then edited and merged it as needed and right
this link from another sheet appeared to me after merging without the first simple quotation mark TEST'!01.

Now after this recommended adjustment of yours
="'TEST'!01"
it already connects fine.

 

[seanxx]

One example is not much to go on but if the col J values will always begin with a single quote mark, would this work for you?

24 03 23.xlsm
	I	J
1		TEST'!01
2	_and_	TEST'!02
3		'TEST'!01_and_'TEST'!02
CONCAT

Cell Formulas
Range	Formula
J3	J3	=CONCAT(I1:I2&"'"&J1:J2)

Thanks for the explanation,
and your option to fix it.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.