Concatenate Text... with spaces

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!
 
M

Mike H

Leslie,

MCONCAT does solve the spaces problem

=MCONCAT(A1:A6," ")

Returns
John Smith Jane Doe John Doe Jane Smith

Mike
 
T

Teethless mama

Try this:

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

ctrl+shift+enter, not just enter
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top