Combine text from multiple cell with some condition

rockybell_77

New Member
Hello,
I have excel sheet where i have huge data. Column A contains vendor code (a number) and column B contains Name of vendor in first cell and Address in subsequent cells in same column B. Number of cells occupied in column B for address is not fixed it can be 2,3 or more.

I want to merge name and address in column B for each vendor code in column A

Example

Cell Column A Column B

<tbody>
</tbody>
 Vendor Code Vendor Name and address 1234 Asia Private Limited -Goa Plot No. M - 14 to M- 18,Verna Industrial estate Goa 91264 Technologies Inc, TX 4501 Attwa, 477590,United States of America

<tbody>
</tbody>

I want to combine B2,b3,b4 in C1, same way B5,B6 in C2 and so on
Can somebody suggest formula to do this task

Last edited:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Is a Macro an Option?

Give this a try in cell C2, copied down, you could then filter column A to exclude null/blank:
Code:
``=IF(A3="",B2 & "; " & C3,B2)``
 Vendor Code Vendor Name and address CalcAddress 1234 Asia Private Limited -Goa Asia Private Limited -Goa; Plot No. M - 14 to M- 18,Verna Industrial estate; Goa Plot No. M - 14 to M- 18,Verna Industrial estate Plot No. M - 14 to M- 18,Verna Industrial estate; Goa Goa Goa 91264 Technologies Inc, TX Technologies Inc, TX; 4501 Attwa, 477590,United States of America 4501 Attwa, 477590,United States of America 4501 Attwa, 477590,United States of America 5678 Marvin Martian Marvin Martian; 123 Conqueror Lane; Canal 4, 3rd level; Southern Hemisphere; Mars; Sol 123 Conqueror Lane 123 Conqueror Lane; Canal 4, 3rd level; Southern Hemisphere; Mars; Sol Canal 4, 3rd level Canal 4, 3rd level; Southern Hemisphere; Mars; Sol Southern Hemisphere Southern Hemisphere; Mars; Sol Mars Mars; Sol Sol Sol

<tbody>
</tbody>

Last edited:

Replies
12
Views
470
Replies
1
Views
455
Replies
4
Views
124
Replies
7
Views
367
Replies
3
Views
315

1,203,757
Messages
6,057,164
Members
444,909
Latest member
Shambles111

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back