multiple cells

[paulie123]

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

 

[iggydarsa]

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]

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

 

[just_jon]

With the Morefunc add in, you can use

=MCONCAT(A1:D1,"_")

The Morefunc add-in is a free download, available at: this site.

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>