Concatenate function

[d488z]

I am trying to add text using the concatenate function....for example=concatenate(a2,b2,c2) which works fine however if I try this =concatenate(a2:c2) comes back with a hashVALUE error....but......

What I am trying to achieve is to extract data from a report which gives me the subject the student is studying, import into excel.... and then in cell (A2) I could have Art and in the next two cells (B2&C2) it is blank and the fourth cell (D2) could have Biology and the next cell blank (E2)...followed by History (F2)

What I would like to see is Art; Biology; History in one cell.

Any help would be much appreciated.

Thanks in advance.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Art[/TD]
[TD][/TD]
[TD][/TD]
[TD]Biology[/TD]
[TD][/TD]
[TD]History[/TD]
[TD]Art; Biology; History[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Chem[/TD]
[TD]RE[/TD]
[TD][/TD]
[TD]PE[/TD]
[TD][/TD]
[TD]Chem; RE; PE[/TD]
[/TR]
[TR]
[TD]Art[/TD]
[TD][/TD]
[TD]RE[/TD]
[TD][/TD]
[TD][/TD]
[TD]History[/TD]
[TD]Art; RE; History[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MARK858]

What version of excel are you using?

 

[Peter_SSs]

If you have the TEXTJOIN function in your version (hence Mark's question) you can use the G2 formula, otherwise try the H2 formula.

Excel Workbook
	A	B	C	D	E	F	G	H
1
2	Art			Biology		History	Art; Biology; History	Art; Biology; History
3		Chem	RE		PE		Chem; RE; PE	Chem; RE; PE
4	Art		RE			History	Art; RE; History	Art; RE; History
Concat

 

[d488z]

Hello Peter

Thank you for help. I am using Excel 2016. The first formula "Text Join" did the job. Saved me hours of learning new functions. Much appreciated. and thanks to Mark858 for taking time to reply to me.

 

[sandy666]

btw TEXTJOIN() is for Excel for Office 365 Excel for Office 365 for Mac Excel for the web Excel 2019 Excel 2019 for Mac but not for Excel 2016

 

[d488z]

Hello Sandy

I have Office 365 at work and office 2011 (Mac) at home. I tried it on my Mac initially when I was trying myself but then tested it at work.

Kind Regards
M Dabbs

 

[sandy666]

sure

have a nice day

FYI: TEXTJOIN()

 

[Peter_SSs]

Hello Peter

Thank you for help. ... The first formula "Text Join" did the job. Saved me hours of learning new functions. Much appreciated.

You are welcome. Thanks for the follow-up.