Combine text from multiple cell with some condition

rockybell_77

New Member
Joined
Feb 14, 2012
Messages
12
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 CodeVendor 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

Thanks in Advance
 
Last edited:

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
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 CodeVendor Name and addressCalcAddress
1234Asia Private Limited -GoaAsia Private Limited -Goa; Plot No. M - 14 to M- 18,Verna Industrial estate; Goa
Plot No. M - 14 to M- 18,Verna Industrial estatePlot No. M - 14 to M- 18,Verna Industrial estate; Goa
GoaGoa
91264Technologies Inc, TXTechnologies Inc, TX; 4501 Attwa, 477590,United States of America
4501 Attwa, 477590,United States of America4501 Attwa, 477590,United States of America
5678Marvin MartianMarvin Martian; 123 Conqueror Lane; Canal 4, 3rd level; Southern Hemisphere; Mars; Sol
123 Conqueror Lane123 Conqueror Lane; Canal 4, 3rd level; Southern Hemisphere; Mars; Sol
Canal 4, 3rd levelCanal 4, 3rd level; Southern Hemisphere; Mars; Sol
Southern HemisphereSouthern Hemisphere; Mars; Sol
MarsMars; Sol
SolSol

<tbody>
</tbody>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,810
Messages
5,525,017
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top