# multiple cells

#### paulie123

##### New Member
Hi, great board here, I have a problem i'm trying to resolve. I need a formula to show the contents of multiple cells in a single cell. e.g i have 4 cells with text in them, "tony", "peter", "will", "phil". i want to show these in a seperate cell so the outcome in the fith cell is "tony_peter_will_phil" any help appreciated! thanks

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### iggydarsa

##### Well-known Member
if you wirite those names on A1, B1, C1, and D1, and you want to see al of them in E1... on E1 write this:

=A1 & " " & B1 & " " & C1 & " " &D1

#### paulie123

##### New Member
simple as that eh? thanks for your quick response, i appreciate it!!

#### just_jon

##### Legend
With the Morefunc add in, you can use

=MCONCAT(A1:D1,"_")

Or load in the ACONCAT Function ( code below ) and substitute ACONCAT for MONCAT in the above.

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> aconcat(a <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, <SPAN style="color:#00007F">Optional</SPAN> sep <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "") <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#007F00">' Harlan Grove, Mar 2002</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>

<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">TypeOf</SPAN> a <SPAN style="color:#00007F">Is</SPAN> Range <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> y <SPAN style="color:#00007F">In</SPAN> a.Cells
aconcat = aconcat & y.Value & sep
<SPAN style="color:#00007F">Next</SPAN> y
<SPAN style="color:#00007F">ElseIf</SPAN> IsArray(a) <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> y <SPAN style="color:#00007F">In</SPAN> a
aconcat = aconcat & y & sep
<SPAN style="color:#00007F">Next</SPAN> y
<SPAN style="color:#00007F">Else</SPAN>
aconcat = aconcat & a & sep
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
</FONT>

Replies
2
Views
388
Replies
6
Views
124
Replies
1
Views
108
Replies
2
Views
257
Replies
12
Views
533

1,195,588
Messages
6,010,606
Members
441,558
Latest member
lambierules

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