Excel concatenation multiple values into a single

Question

Hi guys I'm new to excel and i would like to concatenate the country names into a single cell separated by '/'for the particular corresponding customer as shown in the below

colA1 colB1

A India

A UK

A USA

A Japan
A India/UK/USA/Japan

B India

B UK

B USA

B Japan

B India/UK/USA/Japan

i am expecting the results in the above highlighted row using excel.

Thanks for your help

Answer 1

The easiest way to concatenate cells is by using &. Just use it between cell names and it should work. In order to add other characters in between the text on the concatenated cells (including spaces), use "". So, in order to fulfill your request, you should use something like the below (assuming the cells you want to concatenate are in column A:

=A1&"/"&A2&"/"&A3&"/"&A4

Considering you would like to add "A" to the first word (India) but not to the others, you can try the below formula:

=(A1&"/"&RIGHT(A2,LEN(A2)-2)&"/"&RIGHT(A3,LEN(A3)-2))&"/"&RIGHT(A4,LEN(A4)-2)

It will consider A1 in full, A2 in full minus the first 2 characters and so forth. You can use the same formula for part B if you replace the cells within it. Hope that's what you're looking for.