data in lots of cells

[andrewrgd]

I am designing a staff roster with staff names in cells D9:M32. Is there anyway I can dump all the names into a single cell on another sheet?

Thanks!

 

[jindon]

Hi

UDF
Press Alt+F11 to activate VBE and insert standard module then paste the code

Public Function conc_name(rng As Range, spr As String) As String
Dim r As Range, x As String
    For Each r In rng
        If Not IsEmpty(r) Then
            x = x & r.Text & spr
        End If
    Next
    x = Left(x, Len(x) - Len(spr))
    conc_name = x
End Function

use:

=conc_name(yourRange,"separate string")
where your range would be D9:M32 and separate string would be like " - "
will display with separate string at the end of each name

hope this helps
jindon

 

[andrewrgd]

Thanks, exactly what I wanted!