# Combining cells

#### amberdk

##### New Member
I need to concatenate some cells, but this is tricky and I'm not sure how to do it. Example of data below:

Account: ABC Company
Sales Director 1
Community 1
Community 2
Community 3
Community 4
 Account: XYZ Company Sales Director 2 Community 1 Community 2 Community 3 Community 4

<tbody>
</tbody>
Community 5
Community 6

<tbody>
</tbody>

This comes from a pivot, but there are a a few hundred "Companies" and then each company has a various amount of communities under it. What I need is a concatenation of Company and Community, so I would need for example "Account: ABC Company Community 1" for every single community in the list.

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### mole999

##### Well-known Member
1 col or row per company, then you can \$A\$1 &" "&\$A2 and drag down

#### jtakw

##### Well-known Member
Hi,

Based on your sample data, if you can add a dummy row on top, use B2 formula copied down, without dummy row, put formula in 3rd row as shown in E3 copied down.

Excel 2010
ABCDE
1Account: ABC Company
2Account: ABC Company Sales Director 1
3Sales Director 1Community 1Account: ABC Company Community 1
4Community 1Account: ABC Company Community 1Community 2Account: ABC Company Community 2
5Community 2Account: ABC Company Community 2Community 3Account: ABC Company Community 3
6Community 3Account: ABC Company Community 3Community 4Account: ABC Company Community 4
7Community 4Account: ABC Company Community 4Account: XYZ Company
8Account: XYZ CompanySales Director 2
9Sales Director 2Community 1Account: XYZ Company Community 1
10Community 1Account: XYZ Company Community 1Community 2Account: XYZ Company Community 2
11Community 2Account: XYZ Company Community 2Community 3Account: XYZ Company Community 3
12Community 3Account: XYZ Company Community 3Community 4Account: XYZ Company Community 4
13Community 4Account: XYZ Company Community 4Community 5Account: XYZ Company Community 5
14Community 5Account: XYZ Company Community 5Community 6Account: XYZ Company Community 6
15Community 6Account: XYZ Company Community 6
Sheet1
Cell Formulas
RangeFormula
E3=IF(OR(LEFT(D3,1)={"A","S"}),"",IF(E2="",OFFSET(E3,-2,-1)&" "&D3,LEFT(E2,LEN(E2)-LEN(OFFSET(E3,-1,-1)))&D3))
B2=IF(OR(LEFT(A2,1)={"A","S"}),"",IF(B1="",OFFSET(B2,-2,-1)&" "&A2,LEFT(B1,LEN(B1)-LEN(OFFSET(B2,-1,-1)))&A2))

#### amberdk

##### New Member
Thank you! Works great!!

#### jtakw

##### Well-known Member
You're welcome, welcome to the forum.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,073
Messages
5,857,197
Members
431,862
Latest member
wiz of az

### 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.

### Which adblocker are you using?

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