Concatenate Text... with spaces

  • Thread starter Thread starter Leslie W.
  • Start date Start date
L

Leslie W.

I'm having a problem, and I don't know if there's a solution in the form of
an Excel function. I believe I could write a VB Macro to accomplish what I
need to do, but I can't use macros for this application.

I have a Dataset that looks like this:
John Smith
<blank cell>
Jane Doe
John Doe
<blank cell>
Jane Smith

I need it to concatenate as John Smith, Jane Doe, John Doe, Jane Smith

I have the MCONCAT function, which works, but it does not solve the blank
cell problem. I have also tried the formula suggested by Toothless Mama in
the past, but that doesn't help me with the space.

Any suggestions would be appreciated!

Thanks!
 
Leslie,

MCONCAT does solve the spaces problem

=MCONCAT(A1:A6," ")

Returns
John Smith Jane Doe John Doe Jane Smith

Mike
 
Try this:

=SUBSTITUTE(MCONCAT(IF(A1:A6<>"",A1:A6&", ","")),",","",COUNTA(A1:A6))

ctrl+shift+enter, not just enter
 
Back
Top